So it turns out that SQLite is pretty extreme in assuming that your server's local time is in the UTC timezone, which is something I generally agree with as a best practice but understand cannot be guaranteed.
When we create bookmarks and comments we rely on SQLite to inject the created_at timestamp using the CURRENT_TIMESTAMP helper. This always stores the timestamp using UTC, translating from the system's local time if necessary, and then when you SELECT that field back out using the sqlite node package, you get... a string that doesn't have any kind of timezone attached to it. Javascript assumes that means the timestamp is in the local timezone, which it might not be, and if that's the case everything goes haywire.
This change takes the least-intrusive approach to fixing this by appending the letter 'Z' to the end of timestamps retrieved from the database in the places that we do that. It doesn't feel great but any alternatives I could think of this morning feel likely to cause a lot of pain for people with existing bookmarks databases. We'll just need to make sure that all new JS code that parses SQLite timestamps does the same.
So it turns out that SQLite is pretty extreme in assuming that your server's local time is in the UTC timezone, which is something I generally agree with as a best practice but understand cannot be guaranteed.
When we create bookmarks and comments we rely on SQLite to inject the
created_at
timestamp using theCURRENT_TIMESTAMP
helper. This always stores the timestamp using UTC, translating from the system's local time if necessary, and then when youSELECT
that field back out using the sqlite node package, you get... a string that doesn't have any kind of timezone attached to it. Javascript assumes that means the timestamp is in the local timezone, which it might not be, and if that's the case everything goes haywire.This change takes the least-intrusive approach to fixing this by appending the letter 'Z' to the end of timestamps retrieved from the database in the places that we do that. It doesn't feel great but any alternatives I could think of this morning feel likely to cause a lot of pain for people with existing bookmarks databases. We'll just need to make sure that all new JS code that parses SQLite timestamps does the same.
This closes #59.