node.js - knex and timezone of autoadded dates using knex.fn.now()

Question

I am using knex.js in express. I have one issue when i insert or update some record i get timestamp of server rather than the one i setted in express with below instruction.

process.env.TZ = 'Asia/Karachi';

Though the above works fine as long as i using now in express but when i insert a record the timezone is of server rather than what i specified so i googled and found several solutions. Below is one my migration:

import * as Knex from 'knex';

export async function up(knex: Knex): Promise<void> {
    return knex.schema.createTable('companies', function(table: Knex.TableBuilder) {
        table.bigIncrements().primary();
        table.string('name', 255).notNullable();
        table.text('address').notNullable();
        table.string('slug').notNullable();
        table.string('mobile').notNullable();
        table.enu('status', [ 'enable', 'disable' ]).notNullable().defaultTo('enable');
        table.enu('type', [ 'sole', 'distribution', 'both' ]).notNullable();
        table.timestamp('created_at').defaultTo(knex.fn.now());
        table.timestamp('last_updated_at').defaultTo(knex.fn.now());
    });
}

export async function down(knex: Knex): Promise<void> {
    return knex.schema.dropTableIfExists('companies');
}

As you can see i added knex.fn.now as default time to my migrations when i created them.

As a solution i have tried the below though they didnot work for me:

module.exports = {
    development: {
        client: CLIENT,
        connection: {
            database: DATABASE,
            user: PG_USER,
            password: PASSWORD,
            host: HOST,
            timezone: '+05:00'
        },
        migrations: {
            tableName: 'knex_migrations'
        }
    },

    staging: {
        client: CLIENT,
        connection: {
            database: DATABASE,
            user: PG_USER,
            password: PASSWORD,
            timezone: '+05:00'
        },
        pool: {
            min: 2,
            max: 10
        },
        migrations: {
            tableName: 'knex_migrations'
        }
    },

    production: {
        client: CLIENT,
        connection: {
            database: DATABASE,
            user: PG_USER,
            password: PASSWORD,
            timezone: '+05:00'
        },
        pool: {
            min: 2,
            max: 10
        },
        migrations: {
            tableName: 'knex_migrations'
        }
    }
};

I also tried to set to below under connection of production that didnot worked either.

timezone:"Asia/Karachi"

I have also tried adding a afterCreate MEthod in pool of production but that didnot worked either.

pool: {
    afterCreate: function(connection, callback) {
      connection.query('SET time_zone = Asia/Karachi;', function(err) {
        callback(err, connection);
      });
    }
 }

When i add a record i do not assign date to timestamps so what i am getting in created at is 2021-01-22T02:53:53.000Z while it was 12:am in the morning.

question from:https://stackoverflow.com/questions/65841475/knex-and-timezone-of-autoadded-dates-using-knex-fn-now

Answer 1

Waitting for answers