SQLite Date and Time Functions (2007) | Hacker News Reader

SQLite Date and Time Functions (2007)

www2.sqlite.org

61 points1vuio0pswjnm741 comments

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.

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.

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.

You have two choices.

UNIX epoch time is defined as the number of seconds since 1970/1/1 midnight. You can do those.

I think there is also Julian time, which incorporates the Gregorian skip. It is stored as a floating point number, the integer portion being the day, and the fractional part being the hour.

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')

not exactly convenient, but it indeed does get the job done and is flexible enough to handle whatever you need ... just frustrating that it doesn't exactly follow the ISO spec. Leaving the timezone specifier off makes it ambiguous and should be assumed to be a local time - which it is not (unless you lived in a timezone that was always in UTC).

`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.

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.

It is not allowed to use any other separator than 'T'. You might be thinking about RFC3339

You're absolutely right, I confused it with RFC3339. ISO8601 suggests that the 'T' is optional, but only in the sense of leaving it out... and fails to provide any actual examples of doing so.

The amount of handwaving in ISO8601 could turn a windmill, though in this respect RFC3339 is not much better. The ABNF clearly shows "T" (case-insensitive) as the only separator, but immediately afterward gives us this:

> NOTE: ISO 8601 defines date and time separated by "T". Applications using this syntax may choose, for the sake of readability, to specify a full-date and full-time separated by (say) a space character.

Separated by (say) whatever the hell you want apparently. I'm getting bruises on my face from all the palming.

I use an underbar ("_"). This nicely separates visually a date-time into those two respective parts while keeping them lexically in a single token/string that does not get broken up at line ends.

That is not true. I refer you to a PDF copy of ISO 8601[1] (a 2016 working draft copy, but still representative). Within section "4.3.2 Complete Representations" it reads as follows:

> The character [T] shall be used as time designator to indicate the start of the representation of the time of day component in these expressions. The hyphen [-] and the colon [:] shall be used, in accordance with 4.4.4, as separators within the date and time of day expressions, respectively, when required.

> NOTE By mutual agreement of the partners in information interchange, the character [T] may be omitted in applications where there is no risk of confusing a date and time of day representation with others defined in this International Standard.

They then show examples which clearly show that they mean you can not-include the T, but you CANNOT substitute T for a space.

Unless I am incorrectly reading the document or unaware of a further section indicating the further allowance of substituting a T for a space, you cannot swap T for space according to the standard.

1 - https://www.loc.gov/standards/datetime/iso-tc154-wg5_n0038_i...

Yep, I got it confused with RFC3339, which starts off with a precise spec, then lays out vague exceptions in the prose like using a space. All I want for xmas this year is a proper datetime specification, from anyone but ISO (who still doesn't seem to understand that software engineers are never going to pay for a spec).

You probably mean RFC3339, which overlaps with ISO 8601 and allows this. But the docs don't mention that RFC and only reference the ISO standard.

You can compare the formats here: https://ijmacd.github.io/rfc3339-iso8601/

> JavaScript date parsing assumes that dates that lack the trailing Z are in the client time zone.

The situation is in fact even worse than this. The current (ECMA262) spec states:

> When the time zone offset is absent, date-only forms are interpreted as a UTC time and date-time forms are interpreted as a local time.

This is in fact a huge bug in the spec, which was initially unintentional but is now deliberately preserved for web compatibility reasons.

More info here:

https://maggiepint.com/2017/04/11/fixing-javascript-date-web...

That is good context, thanks! Once I found my bug I had a moment of not being sure who to blame. Honestly, I'm a bit surprised that the ISO8601 spec dictates that absent the Z the date-time ought to be interpreted as local. At the least, I expected there would at least be a way for me to say "trust me JS, this date is UTC so please parse it that way" - but the only way I could find to force that to happen was to manually add a "Z".

But the insanity of inconsistently choosing local/UTC based on the presence of time is genuinely painful. Dates and times are hard enough as it is, why would they do that? It gives me some amusement that this was one of the motivating use cases behind the Temporal spec.

> I'm a bit surprised that the ISO8601 spec dictates that absent the Z the date-time ought to be interpreted as local

My understanding is that an ISO8601 timestamp with no offset simply means that no time zone is specified, like what the JS Temporal API calls a PlainDateTime. This is a reasonable concept, and to avoid footguns it should be impossible to implicitly convert such a timestamp to a timestamp with a time zone.

This concept isn’t representable as a JS Date, which will always have a time zone specified (always the local time zone).

> At the least, I expected there would at least be a way for me to say "trust me JS, this date is UTC so please parse it that way" - but the only way I could find to force that to happen was to manually add a "Z".

FWIW, I always use parseISO from date-fns for this. I agree it’s bonkers that this is necessary!

https://date-fns.org/v4.1.0/docs/parseISO

Why not link to the most recent version?

https://sqlite.org/lang_datefunc.html

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

The main advantage of string datetimes is that you can decipher what they mean just by looking at them in a table.

Just CREATE VIEW something (ahead of time, so it's ready) for the rare time you need to visually inspect it.

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.

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.

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?

Timezones can change.

Units of time can also change. It's possible that a day of 10 hours of 100 minutes could be legislated. Not likely, but possible.

This isn't a very good rebuttal, because one of these things (timezone change) happens quite frequently and the other (changes to units of time) hasn't happened in any noticable scale.

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.

As I said, because time zone definitions change.

If daylight savings time gets cancelled by legislation, then the event happening at noon two summers from now, you will still probably want to happen at noon -- the new noon.

But changes to timezones don't apply retroactively. At least not in this universe!

UTC is pretty stable though. I recall they will obsolete leap seconds somewhere in the next 10 years

If users expect functionality for filtering by year or year-month, having indexed string date/time makes implementation trivial.

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...

We do this as well, but the main drawback is that your DB is "unusable" without those functions, and the functions are application side only.

So if you get a DB from somewhere else and want to open it in whatever tool you use for visualisation, all the queries/views/... will not work because they don't know about all those functions.

sqlite is pretty great, but I have to admit the main reason I keep using postgres, even in situations where sqlite would probably be a better fit, is that I like the postgres standard function library better.

But I also use postgres as a sort of better excel, so what do I know. My desktop instance has a million silly small tables, you know back of envelope ideas, exploratory data, to do lists, etc

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.

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.

There's just enough there to be able to do "+1 month" calculations and similar, which is really helpful for generating dates in a range.

Ignoring time zones, the Boris Johnson approach to time zones.

The speed of sqlite3 is good

Something like

   echo "select datetime(time,'unixepoch'),id from t1"|sqlite3 0.db

Sometimes I need to convert dates in text files to or from unixepoch^1

IME, this is at least 2x as slow as converting dates with sqlite3

For example, I have a text file with the following format

domainname ipv4-address # unixepoch

I use a simple filter something like this ("yy094")

    /*
      strftime<-->strptime 
    */
    int fileno (FILE *);
    FILE *f;
    #define jmp (yy_start)=1+2*
    #include <time.h>
    char *strptime(const char *s, const char *f, struct tm *tm);
    struct tm t;
    typedef long int time_t;
    time_t xt; 
    char zt[26];
    struct tm *yt;
   a Mon|Tue|Wed|Thu|Fri|Sat|Sun
   d (0[1-9]|[12][0-9]|3[01])
   b Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec
   m 0[1-9]|1[012]
   Y 19[2-9][0-9]|[2-9][0-9]{3}
   H [0-9]{2}
   M [0-9]{2}
   S [0-9]{2}
   Z [+-][0-9]{2,4}|[A-EG-KMNPSTUWZh]{2,3}[CTAK]
    int xd70f5083A()
    {
    printf("%ld",mktime(&t));
    return 0;
    }
    int x2146ea7d()
    {
    xt=atoi(yytext);
    if((xt<-2147483646)||(xt>2147483648))
    {fwrite(yytext,1,yyleng,yyout);goto s;}
    yt=localtime(&xt);
    strftime(zt,sizeof(zt),"%d %b %Y %H:%M:%S %Z",yt);
    fwrite(zt,sizeof(zt),1,stdout);
    return 0;
    s:return 1;
    }
   %option nounput noinput noyywrap
   %%
   [12][0-9]{9} {
    x2146ea7d();
    }
   {d}[ ]{b}[ ]{Y}[ ]{H}:{M}:{S}[ ]{Z} {
    strptime(yytext,"%d %b %Y %H:%M:%S %Z",&t);
    xd70f5083A();
    }
   {d}[ ]{b}[ ]{Y}[ ]{H}:{M}:{S} {
    strptime(yytext,"%d %b %Y %H:%M:%S",&t);
    xd70f5083A();
    }
   {a}[ ]{b}[ ]{d}[ ]{H}:{M}:{S}[ ]UTC[ ]{Y} {
    strptime(yytext,"%a %b %d %H:%M:%S UTC %Y",&t);
    xd70f5083A();
    }
   {Y}-{m}-{d}T{H}:{M}:{S} {
    strptime(yytext,"%Y-%m-%dT%H:%M:%S",&t);
    xd70f5083A();
    }
   {Y}-{m}-{d}[ ]{H}:{M}:{S} {
    strptime(yytext,"%Y-%m-%d %H:%M:%S",&t);
    xd70f5083A();
    }
   .|\n+ fwrite(yytext,1,yyleng,yyout);
   %%
    int main(int argc, char *argv[])
    { 
    if(argc>1)if(argv[1])
    if(argv[1][0]==45)
    if(argv[1][1]==104)
    {
    putc(10,stdout);
    puts("   usage: yy094 < file");
    puts("   zones: UTC only"); 
    puts("   range: 09 Sep 2001 01:46:40 - 19 Jan 2038 03:14:07");
    puts("   input formats:");
    puts("    2147483647");
    puts("    09 Sep 2001 01:46:40");
    puts("    09 Sep 2001 01:46:40 UTC");
    puts("    2001 Sep 09 01:46:40");
    puts("    2001-Sep-09T01:46:40");
    puts("    09 Sep 2001 01:46:40 UTC");
    puts("    Sun Sep 09 01:46:40 UTC 2001");
    puts("   output formats:");
    puts("    19 Jan 2038 03:14:07 UTC");
    putc(10,stdout);
    goto x;
    }
    yylex();
    x:exit(0); 
    }

Another input format, for HTTP response headers:

   {a}[,][ ]{d}[ ]{b}[ ]{Y}[ ]{H}:{M}:{S}[ ]GMT {
    strptime(yytext,"%a, %d %b %Y %H:%M:%S GMT",&t);
    xd70f5083A();
    }
      
    puts("    Sun, Sep 09 2001 01:46:40 GMT");

Reply on news.ycombinator.com