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.

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

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

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.

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

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); 
    }

[dead]