postgresqltimestampsupabase

What is the best plain vanilla timestamp format to save in database (coming from Javascript (PostgreSQL/ Supabase)?


The authors of the npm package Moments state that Moments is deprecated and in maintainance mode only.

The authors recommend we move away from Moments; while it does recommend a few other libraries, I would prefer, if possible, to save the data as plain vanilla Javascript objects like those returned by new Date().

Is there a recommended approach to save the data?

As I mentioned, I would like to save the data as close to the plain vanilla new Date(); as possible.

But I am happy to save whatever other data is necessary to keep track of the data and make it future proof and (reasonably speaking) not need to use extra libraries in the future.

Is there a good rubric to use to figure out which format to save?


Solution

  • TDLR; it is best to save the timestamp in these generally accepted technical formats and then on the client-side handle readability (read: store the UTC or ISO format to DB then handle readability locally either manually or with a library like moments or luxon (a "successor" to moments)

    Also, TLDR; what you save in the db, once you pull it to the client, you can remake into a javascript Date object like this: new Date(downloadDateTime) so you can use the date object like you would a standard const date = new Date();

    Here are the two main formats you'd likely consider saving to your DB (UTC and ISO respectively) and how to create them with plain vanilla Javascript.

    const dateUTC = new Date();
    const dateISO = dateUTC.toISOString(); // or const dateISO = new Date().toISOString;
    

    UTC is basically Coordinated Universal Time and the time at 0 degrees longitude (the Prime Meridian); and it is not affected by DST.

    Disambiguation: It is also sometimes called Greenwich Meridian Time (GMT); Greenwich is a city in England (~8 miles east of London) that the Prime Meridian historically runs through and so sometimes you see it is as GMT. That this would be 0 degrees longitude was voted on by committee in 1884. (ie. it's a voted upon convention.) Also, in plane and ship navigation it is often called Zulu time (Zulu which is the radio phonetic alphabet for Z). Read more here.

    This is an example of a timestamp in 2023-11-07T21:01:44.000Z. (And now you know why there is a Z at the end of it.)


    The ISO 8601 format is recognized worldwide and the format is similar to the UTC format except that it includes timezone info as well.

    (Recognized Worldwide? Why are you telling me that? Because formatting timestamp data across timezones can get a little hairy in different places; for example, they might save AM/PM before the time etc, and so ISO formatting just makes it clean. You use this format to save the data in db and then you can locally translate it to display the way you'd like.

    Here are a few examples of ISO timestamps and notice there is either a Z or a +/- for the time offset from the UTC time time. "2023-11-07T21:01:44.000Z" (UTC time), "2023-11-07T21:01:44.000-05:00" (Eastern Standard Time), or "2023-11-07T21:01:44.000+02:00" (Central European Time).

    Read more here on ISO timestamps.

    And read this question to get more nuanced info on timezones, Standard Time vs. Daylight saving, etc.

    It is pretty safe to save your timestamp as UTC new Date() in general if you're logging timestamps. And in your database, in Supabase, you'd want to save it under the format timestampz. Also, with the UTC time, libraries like moment, moment-timezones, and luxon (a "successor" to moments) - can work with that data and parse as you need in most cases.

    (Optional) However, if you're going completionist and don't fully know all your requirements yet of the data you want to save you can also save the following:

    Note the format of the below list is Description, Javascript function, and datatype in postgreSQL/Supabase.

    • UTC new Date() timestampz
    • ISO (new Date()).toISOString() timestampz
    • Timezone Intl.DateTimeFormat().resolvedOptions().timeZone; text
    • Time offset - but this is best done through a library like luxon or moment-timezone which make this easier - save this in minutes int2