4 ms·

`RETURNING` will substantially clean up my code, and I already have one migration which could have just been a `DROP COLUMN`, so this is great news. On the sub

`RETURNING` will substantially clean up my code, and I already have one migration which could have just been a `DROP COLUMN`, so this is great news. On the subject of "it's called 'lite' for a reason", my wishlist does include library functions for working with RFC3339 timestamps. SQLite already ships with a fairly large suite of JSON tools, which are optional to compile into the library, so there's precedent. Datetimes are of those things which is incredibly annoying to get right, and really belongs inside the database. RFC3339 timestamps are already well designed, since if you stick to UTC (and if you don't store timezone data separately you deserve those problems), lexical order is temporal order, but queries which would be rendered in English as "return all accounts where last payment is ninety days prior to `now`" isn't really possible with string comparisons. Also, with the JSON library, you can use a check constraint to fail if a string isn't valid JSON, another affordance I would love to have for datetimes. Grateful for what we just got, though! Just daydreaming...

nalgeon 6y ago
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
porker 6y ago
Yes native date and time handling would be awesome. I dislike dealing with them as strings. And then temporal tables. And natively versioned tables. I can dream...
bob1029 6y ago
I've been storing my datetimes as 64 bit unix timestamps. If I need any additional timezone information, it lives in additional columns. The only edge case we have just requires looking at the user's timezone to project certain facts correctly.

nalgeon 6y ago

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

porker 6y ago

Yes native date and time handling would be awesome. I dislike dealing with them as strings. And then temporal tables. And natively versioned tables. I can dream...

bob1029 6y ago

I've been storing my datetimes as 64 bit unix timestamps. If I need any additional timezone information, it lives in additional columns. The only edge case we have just requires looking at the user's timezone to project certain facts correctly.