5 ms·

SQLite has ISO-8601 compatible date functions, isn't that enough? sqlite> select datetime('now', '-90 days'); 2020-12-12 21:44:22 https://sqlite.org/lang_

SQLite has ISO-8601 compatible date functions, isn't that enough? sqlite> select datetime('now', '-90 days'); 2020-12-12 21:44:22 https://sqlite.org/lang_datefunc.html https://sqlite.org/lang_datefunc.html

rover0 6y ago
Without a timezone the time is meaningless for many purposes.
- pupdogg 6y ago
  We store 8 million transactions a day in UTC...customer time zone is merely an integer offset stored alongside...simple and easy, what’s wrong with that?
  - Osiris 6y ago
    The customer's offset is always changing but I assume you mean you're storing the offset as it was at the moment of the transaction. How do you deal with the twice a year time offsets when a transaction occured first but shows up after because the clock rolled back an hour? Isn't it generally better to store to the full timezone like America/New_York?
    - pupdogg 6y ago
      For daylight savings, the offset is generated in real-time in our app at the time of transaction and then stored inside the db...so if 2x TXs occurred in CST at 1:58am (1 before and 1 after DST went into effect at 2am) they would both show up as being between 1-2am on the day of time change. However, the spike in data would show the anomaly for our use case. For a more mission critical app, you could have an additional tinyint field that would get set as 1 for all transactions that occurred in these transitional periods to be able to show data with or without the daylight savings.
samatman 6y ago
Beats a swift kick in the pants! You're right, that was a bad example. Maybe it's just me, but I've never figured out how to get SQLite to do a query like "select from orders where the order was on a Tuesday in Pacific time". I don't think you can; that requires predicates, and all I see are strftime and some useful pre-cooked variations on it.
- simonw 6y ago
  It looks like this works, but it's pretty gnarly. I have datetimes stored in UTC, so I first needed to convert them to PST by applying the 8 hour time difference. Then I used strftime('%w') to get the day of week (as a number in a string). Then I can filter for that equalling '4' for Thursday. select author_date, datetime(author_date, '-8 hours') as author_date_pst, strftime('%w', datetime(author_date, '-8 hours')) as dayofweek_pst, * from commits where dayofweek_pst = '4' -- Thursday You can try the query out here: https://github-to-sqlite.dogsheep.net/github?sql=select+author_date%2C%0D%0Adatetime%28author_date%2C+%27-8+hours%27%29+as+author_date_pst%2C+%0D%0Astrftime%28%27%25w%27%2C+datetime%28author_date%2C+%27-8+hours%27%29%29+as+dayofweek_pst%2C%0D%0A*+from+commits%0D%0Awhere+dayofweek_pst+%3D+%274%27+--+Thursday+ https://github-to-sqlite.dogsheep.net/github?sql=select+auth...
  - samatman 6y ago
    On the one hand: very impressive! On the other hand: Wrong from March 14th to November 7th of this year. This is where a richer standard library of datetime functions would come in handy. Another example: strftime's %W starts on the first Monday of the year, when the ISO week calendar starts the first Monday of the week containing the first Thursday (so the first week is sometimes found in the previous calendar year).
    - simonw 6y ago
      Hah, yeah PST v.s. PDT does make this harder. At that point I'd switch to using additional custom SQL functions. I wrote a plugin called datasette-dateutil https://datasette.io/plugins/datasette-dateutil https://datasette.io/plugins/datasette-dateutil that exposes some extra functions, but it doesn't have anything useful for timezone calculations yet. I've opened an issue: https://github.com/simonw/datasette-dateutil/issues/4 https://github.com/simonw/datasette-dateutil/issues/4

rover0 6y ago

Without a timezone the time is meaningless for many purposes.

pupdogg 6y ago
We store 8 million transactions a day in UTC...customer time zone is merely an integer offset stored alongside...simple and easy, what’s wrong with that?
- Osiris 6y ago
  The customer's offset is always changing but I assume you mean you're storing the offset as it was at the moment of the transaction. How do you deal with the twice a year time offsets when a transaction occured first but shows up after because the clock rolled back an hour? Isn't it generally better to store to the full timezone like America/New_York?
  - pupdogg 6y ago
    For daylight savings, the offset is generated in real-time in our app at the time of transaction and then stored inside the db...so if 2x TXs occurred in CST at 1:58am (1 before and 1 after DST went into effect at 2am) they would both show up as being between 1-2am on the day of time change. However, the spike in data would show the anomaly for our use case. For a more mission critical app, you could have an additional tinyint field that would get set as 1 for all transactions that occurred in these transitional periods to be able to show data with or without the daylight savings.

samatman 6y ago

Beats a swift kick in the pants! You're right, that was a bad example. Maybe it's just me, but I've never figured out how to get SQLite to do a query like "select from orders where the order was on a Tuesday in Pacific time". I don't think you can; that requires predicates, and all I see are strftime and some useful pre-cooked variations on it.

simonw 6y ago
It looks like this works, but it's pretty gnarly. I have datetimes stored in UTC, so I first needed to convert them to PST by applying the 8 hour time difference. Then I used strftime('%w') to get the day of week (as a number in a string). Then I can filter for that equalling '4' for Thursday. select author_date, datetime(author_date, '-8 hours') as author_date_pst, strftime('%w', datetime(author_date, '-8 hours')) as dayofweek_pst, * from commits where dayofweek_pst = '4' -- Thursday You can try the query out here: https://github-to-sqlite.dogsheep.net/github?sql=select+author_date%2C%0D%0Adatetime%28author_date%2C+%27-8+hours%27%29+as+author_date_pst%2C+%0D%0Astrftime%28%27%25w%27%2C+datetime%28author_date%2C+%27-8+hours%27%29%29+as+dayofweek_pst%2C%0D%0A*+from+commits%0D%0Awhere+dayofweek_pst+%3D+%274%27+--+Thursday+ https://github-to-sqlite.dogsheep.net/github?sql=select+auth...
- samatman 6y ago
  On the one hand: very impressive! On the other hand: Wrong from March 14th to November 7th of this year. This is where a richer standard library of datetime functions would come in handy. Another example: strftime's %W starts on the first Monday of the year, when the ISO week calendar starts the first Monday of the week containing the first Thursday (so the first week is sometimes found in the previous calendar year).
  - simonw 6y ago
    Hah, yeah PST v.s. PDT does make this harder. At that point I'd switch to using additional custom SQL functions. I wrote a plugin called datasette-dateutil https://datasette.io/plugins/datasette-dateutil https://datasette.io/plugins/datasette-dateutil that exposes some extra functions, but it doesn't have anything useful for timezone calculations yet. I've opened an issue: https://github.com/simonw/datasette-dateutil/issues/4 https://github.com/simonw/datasette-dateutil/issues/4