Javascript and postgres: dates with microseconds

Javascript dates only supports milliseconds.

In case you need your code to read a date with microseconds resolution, you’ll find the Javascript PostgreSQL libraries probably mapping internally PostgreSQL timestamp into javascript dates. So you’ll lose microseconds coming from the database, and the INSERT also won’t allow to specify microseconds.

This what happens with npm postgres js library that I used.

One solution I found is keeping the dates as strings in the js code, and letting them converted with PostgreSQL functions.

Convert before an INSERT

use TO_TIMESTAMP passing the string value

import postgres from 'postgres';
const sql = postgres({...});

const row = {
  // ... other fields ...
  columna: 'columnnValue',
  // convert date into Postgrees timestamp
  created: sql`TO_TIMESTAMP('2024-01-19 14:59:48.662306+00', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"')`
}


// 'created' field is a Postgres timestamp
// this code returns its string format with microseconds
 await sql`INSERT into mytable ${sql(row, Object.keys(row))}`;

Converta after reading a SELECT

use TO_CHAR to format the timestamp into a string

import postgres from 'postgres';
const sql = postgres({...});

// 'created' field is a Postgres timestamp
// this code returns its string format with microseconds
const records = await sql`SELECT 
        ${sql(fields)},
        ${sql`TO_CHAR(created, 'YYYY-MM-DD"T"HH24:MI:SS.US')  || 'Z' AS created`}
      FROM mytable`;

Manipulating date with microseconds

In case you just need to insert and return dates and you happy to keep them as strings, the code above will suffice.

To create dates, e.g. the current timestamp, you can use Postgres now() function.

In case you need to manipulate dates at the code level, you can use google precise date, that handles dates up to the nanoseconds. This is even more than the required microseconds resolution so not sure it’s the best solution. I’ve read of luxon, another js date libraries working with microseconds, so that might work better for you.

Clap if useful, follow me for more.