Re:SQLite Release 3.37.0
by hnarn on Tuesday November 30, 2021 @05:39AM
(#29390788)
> In my experience human readability is pretty important when you are debugging or working with SQL in a raw form (running random, ad-hoc queries).
I agree, but most databases have functions for this. MySQL example:
> SELECT FROM_UNIXTIME(1196440219);
-> '2007-11-30 10:30:19'
I would claim this reinforces the benefit of unix timestamps because now you're getting it back in your local time (or whatever you choose to convert it to: SET time_zone), not whatever time it happened to be put into the database as.
For MySQL there's a more important point though:
* Storing timestamps in MySQL as pure text is simply wrong since MySQL has abstractions for timestamps (like the TIMESTAMP type)[1] and not using this is a completely unnecessary violation of good practice
* In the case we're discussing (no abstractions available, INT only) I would still say that unix timestamps brings the rather huge benefit of ensuring that all data is put in correctly: there is no way to sanitize inputs with a string column and ensure that the same timezone is always used, at least not without a bunch of extra an unnecessary code.
I agree, but most databases have functions for this. MySQL example:
> SELECT FROM_UNIXTIME(1196440219);
-> '2007-11-30 10:30:19'
I would claim this reinforces the benefit of unix timestamps because now you're getting it back in your local time (or whatever you choose to convert it to: SET time_zone), not whatever time it happened to be put into the database as.
For MySQL there's a more important point though:
* Storing timestamps in MySQL as pure text is simply wrong since MySQL has abstractions for timestamps (like the TIMESTAMP type)[1] and not using this is a completely unnecessary violation of good practice
* In the case we're discussing (no abstractions available, INT only) I would still say that unix timestamps brings the rather huge benefit of ensuring that all data is put in correctly: there is no way to sanitize inputs with a string column and ensure that the same timezone is always used, at least not without a bunch of extra an unnecessary code.
[1]: https://dev.mysql.com/doc/refman/8.0/en/datetime.html