Knex NodeJS insert date timezone compatible inside PostgreSQL

Question

I have a postgreSQL table like this:

table.string('id');
table.string('name');
table.specificType('data', 'JSONB');
table.timestamp('runDate');
table.boolean('done').default(false);

I wonder what is the safe way to insert date time inside the database.

This is what I do:

await _i_.knex("jobs")
    .transacting(ctx ? ctx.transaction : null)
    .insert({
        id: job.id,
        name: job.name,
        data: job.data,
        id: job.id,
        runDate: job.runDate,
        done: false
        });

When I want to query my table, I use:

  return await _i_.knex('jobs')
        .transacting(ctx ? ctx.transaction : null)
        .whereRaw('"runDate" < NOW()')
        .andWhere('done', false)
        .returning("*")
        .update({
            done: true
        });

So I can have issue with the timezone if my nodeJS server doesn't have the save timezone than my PostgreSQL.

How do you manage that?

Answer 1

By default knex creates timestamp with time zone (timestamptz) type for .timestamp() columns for PostgreSQL. So, your date and time are stored with timezone. For inserting timestamp values I like to use moment package.

const moment = require('moment');
const dateStr = moment().utc().format();
console.log(dateStr); // => Date in format 'YYYY-MM-DDTHH:mm:ssZ'
knex.insert({ runDate: dateStr })