Knex with Postgres unintentionally saves the timestamp

Question

I am using the Knex query designer with Postgres in my application. I'm trying to add fields created_atand updated_atin my database with formatted data ISO8016 in UTC. I am trying to make my data look like this:

2017-04-20T16:33:56.774Z

In my Knex migrations, I tried to use the method .timestamps(), creating both created_at, and updated_atmanually using the .timestamp()  method and calling them myself.

When I mortgage my database and install created_atand updated_atequal moment().utc().toISOString(), but it is stored in my database as follows:

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

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

Answer 1

Postgres saves the timestamp in an internal format, and when you read it, it displays it in the format you request.

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 time window 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 this is how it is 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);
      });
    }
 }
});