SQLite Date and Time Functions (2007)
23 comments
·June 14, 2025stillpointlab
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.
ncruces
Try one of these:
strftime('%Y-%m-%dT%H:%M:%SZ')
strftime('%Y-%m-%dT%H:%M:%fZ')
strftime('%Y-%m-%dT%H:%M:%fZ', column, 'auto')nikeee
`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
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.
user7266
[dead]
noitpmeder
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
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
Why not link to the most recent version?
SJC_Hacker
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
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.
needusername
Ignoring time zones, the Boris Johnson approach to time zones.
biofuel5
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
The main advantage of string datetimes is that you can decipher what they mean just by looking at them in a table.
o11c
Just CREATE VIEW something (ahead of time, so it's ready) for the rare time you need to visually inspect it.
crazygringo
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.
jiggunjer
UTC is pretty stable though. I recall they will obsolete leap seconds somewhere in the next 10 years
hudsonja
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?
pgwhalen
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.
jbverschoor
Timezones can change.
bob1029
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.
vadivlkumar
[dead]
null
One huge benefit of using SQLite over a traditional server/client DBMS is the ability to easily add SQL functions that call into your host language and memory-space.
For example, we’re using better-sqlite3 which has a convenient API for adding SQL functions [1], and we have dozens of helper methods for dealing with time using the temporal-polyfill module.
We have custom JSON-based serialisation formats for PlainDate, PlainTime, PlainDateTime, ZonedDateTome, etc. Then in SQL we can call ‘isDate_Between__(a, b, c)`.
a, b, and c are deserialised by the JS (TS) function, the logic is run, and the result is returned to SQLite. We’ve had no performance issues with this approach, though we’re only dealing with simple CRUD stuff. No big data.
You can even use these functions with generated columns and indexes, but I haven’t found a proper use for this yet in my work.
[1] https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/ap...