Knex with Postgres storing timestamp in unintended ways

Question

I am using Knex query builder with Postgres in my application. I am trying to add a created_at and updated_at fields in my database with ISO8016 formatted data in UTC time. I am trying to have my data look like this:

2017-04-20T16:33:56.774Z

In my Knex migrations, I have tried using both the .timestamps() method creating both created_at and updated_at manually with the .timestamp() method and naming them myself.

When I seed my database and set created_at and updated_at equal to moment().utc().toISOString(), but it stores in my database as this:

2017-04-20 11:20:00.851-05

There is something between the code and the database changing the data and I don't know if it's Knex, the Postgres Node library, or Postgres itself.

Answer 1

Postgres stores timestamp in internal format and when you read it it shows it in the format that you request it.

knex_test=# update accounts set created_at = '2017-04-20T16:33:56.774Z';                                                                                                                                                                                                                                                  UPDATE 47
knex_test=# select created_at from accounts where id = 3;
         created_at         
----------------------------
 2017-04-20 19:33:56.774+03
(1 row)

knex_test=# \d accounts
                                       Table "public.accounts"
   Column   |           Type           |                          Modifiers                          
------------+--------------------------+-------------------------------------------------------------
 id         | bigint                   | not null default nextval('test_table_one_id_seq'::regclass)
 last_name  | character varying(255)   | 
 email      | character varying(255)   | 
 logins     | integer                  | default 1
 about      | text                     | 
 created_at | timestamp with time zone | 
 updated_at | timestamp with time zone | 
 phone      | character varying(255)   | 
Indexes:
    "test_table_one_pkey" PRIMARY KEY, btree (id)
    "test_table_one_email_unique" UNIQUE CONSTRAINT, btree (email)
    "test_table_one_logins_index" btree (logins)

knex_test=# 

You can change in which timezone postgres returns timestamps for your connection with

knex_test=# SET timezone = 'UTC';
SET
knex_test=# select created_at from accounts where id = 3;
         created_at         
----------------------------
 2017-04-20 16:33:56.774+00
(1 row)

knex_test=# 

And here is how its done with knex https://github.com/tgriesser/knex/issues/97

var knex = Knex.initialize({
  client: 'pg',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test',
  },
  pool: {
    afterCreate: function(connection, callback) {
      connection.query('SET timezone = timezone;', function(err) {
        callback(err, connection);
      });
    }
 }
});