[HN Gopher] SQLite Date and Time Functions (2007)
___________________________________________________________________
SQLite Date and Time Functions (2007)
Author : 1vuio0pswjnm7
Score : 39 points
Date : 2025-06-14 04:54 UTC (1 days ago)
(HTM) web link (www2.sqlite.org)
(TXT) w3m dump (www2.sqlite.org)
| needusername wrote:
| Ignoring time zones, the Boris Johnson approach to time zones.
| biofuel5 wrote:
| I just store millis or nanos as INTEGER. Never found the correct
| use for string datetimes, also they're slower and take much more
| space
| simonw wrote:
| The main advantage of string datetimes is that you can decipher
| what they mean just by looking at them in a table.
| o11c wrote:
| Just CREATE VIEW something (ahead of time, so it's ready) for
| the rare time you need to visually inspect it.
| bob1029 wrote:
| This is the best path in my experience. I typically store
| timestamps as 64-bit unix seconds in the same way.
|
| On the application side, I use Dapper and DateTimeOffset to map
| these to a domain type with proper DateTime/UTC fields.
|
| I've found that storing time as integers in the database has
| some interesting upsides. For example, range queries over the
| field tend to be faster.
| crazygringo wrote:
| For storing actual moments in physical time (especially past
| events), and where the time zone is irrelevant, for sure.
|
| But for storing future events that are tied to a time zone, you
| need the string with time zone. Otherwise when time zone
| definitions change, your time will become wrong.
| hudsonja wrote:
| Timezones just give you a set of rules to determine a
| cultural description of a given point in time. How is
| timezone any more or less relevant to a future vs. past
| event?
| jbverschoor wrote:
| Timezones can change.
| pgwhalen wrote:
| The cultural rules tend to be more important when
| describing future events, where the "human friendly"
| description is what really defines it.
|
| When describing past events, it's often most precise to
| describe the literal universe time that it happened.
|
| Obviously these are just generalities, whether you choose
| one strategy or another depends on the specific use case.
| stillpointlab wrote:
| The fact that they do not include the trailing 'Z' for UTC
| timestamps is a frustration for me. It caused a few hours of
| debugging since JavaScript date parsing assumes that dates that
| lack the trailing Z are in the client time zone. I had to add a
| hack to check if a UTC date did or did not have the trailing Z
| and append the Z if it was missing.
|
| This is made worse when you have a lot of `createdAt` columns
| that get set to NOW. You have to deal with the missing Z in all
| places where it matters. And in general, it is pointless to use
| the `localtime` parameter since that is the server time, and for
| UI I want to display the time local for the user. So I want to
| deal exclusively in UTC on the server and do any time zone
| conversions on the client.
|
| Worth noting that when I changed to PostgreSQL, its date function
| does add the Z which makes life easier in general. But it is an
| inconsistency to be aware of if you use both DBs.
| noitpmeder wrote:
| Seems it should be trivial to extend/change the data type to
| add a Z. It's not like it's storing the ISO8601 string in the
| db itself, so it's just a presentation later that is giving you
| the string.
| em500 wrote:
| You don't actually know how they're stored. SQLite has a
| rather idiosyncratic approach to datetimes: it does not
| provide any datetime data types (the only SQLite data types
| are NULL, INTEGER, REAL, TEXT and BLOB). It's left entirely
| to the user how to store datetimes using these types. What
| SQLite does provide are functions (documented on the
| submitted page) that translate some datetime representations
| (stored using the one of the mentioned basic datatypes) to
| other formats. So you can choose to store your datetimes in
| unix-epoch INTEGER and use the translation functions to
| output ISO8601 TEXT when needed, or the other way around:
| there is no correct or even preferred way in SQLite.
| ncruces wrote:
| Try one of these:
| strftime('%Y-%m-%dT%H:%M:%SZ')
| strftime('%Y-%m-%dT%H:%M:%fZ')
|
| You can use this to convert whatever internal format you're
| using for presentation, in a SELECT statement. Like so (be sure
| to read up on 'auto', to see if it fits):
| strftime('%Y-%m-%dT%H:%M:%fZ', column, 'auto')
| nikeee wrote:
| `current_timestamp` also returns something like `2025-06-15
| 19:50:50` while the docs state that it is ISO 8601. Except that
| this is not ISO 8601 due to the T missing in the middle. This
| has caused some headaches due to different formats of JS's
| `.toISOString()` and SQLite's `current_timestamp`. The datetime
| column is basically only for documentation. I wish they had
| some timestamptz type which rejects insertions containing
| invalid datetime formats.
| chuckadams wrote:
| ISO8601 is a collection of different formats, and using a
| space instead of a 'T' is one of the allowed variations. I'm
| not sure anything implements the full spec perfectly.
| ncruces wrote:
| Why not link to the most recent version?
|
| https://sqlite.org/lang_datefunc.html
| SJC_Hacker wrote:
| They probably should have just omitted date/time functionality
| completely, keeping in spirit the "Lite" in SQLite. Their
| implementation is so bare bones as to be nearly useless compared
| to say PostgreSQL.
|
| Users could then just use either client or user created functions
| to do the conversion, in whatever makes sense for the app. If all
| you need is GMT, just store seconds/milliseconds etc. from epoch.
| If you want to store older dates like in a historical database,
| strings or day/month/year split or even just single integer. Name
| columns appropriately to avoid ambiguity, like "gmt_ms" and it
| shouldn't cause too many problems.
| dardeaup wrote:
| I disagree. I think that date/time data is pervasive enough to
| even warrant having built-in column data types for them. It's
| helpful when you care about data integrity.
___________________________________________________________________
(page generated 2025-06-15 23:00 UTC)