postgres_datetime_gotchas.md

First of all, lets understand few date-time standard definitions.

ISO Time String

The standard that we are talking about is formally named ISO 8601:1988 but is popularly also called ISO 8601

ISO 8601 also has support for

• years before 0 AD or after 9999
• Week based dates
• Ordinal dates

But lets not talk about all those now. We will be considering more faimiliar way of using the calender date.

Though there are few allowed varations to the parts of an ISO time string, but we will stick to just one most popular standard of having a complete calender date with - as separator.

Depending on how much details you have about your time instant, ISO time string has following main parts

• years
  • YYYY
• calendar date
  • YYYY-MM-DD
  • YYYYMMDD
    • rearely used
• time
  • hh:mm:ss.sss
  • hh:mm:ss
  • hh:mm
• timezone
  • Time zones in ISO 8601 are represented as UTC or as an offset from UTC.
  • Z
    • Z is the zone designator for the zero UTC offset
  • ±hh:mm
  • ±hhmm
  • ±hh
    • UTC offest in hours, very rarely used

Summarizing it all... the mostly seen ISO time strings are following,

• YYYY-MM-DDThh:mm:ss:SSSZ
• YYYY-MM-DDThh:mm:ssZ
• YYYY-MM-DDThh:mm:ss:SSS+00:00
  • or a rarer YYYY-MM-DDThh:mm:ss:SSS+0000
• YYYY-MM-DDThh:mm:ss+00:00
  • or a rarer YYYY-MM-DDThh:mm:ss+0000

Lets consider a time-instant of Mon Dec 19 2016 12:00:00 in India. When we represent this in ISO 8601, it can be any of following,

• 2016-12-19T12:00:00:000+05:30
  • or a rarer 2016-12-19T12:00:00:000+0530
• 2016-12-19T12:00:00+05:30
  • or a rarer 2016-12-19T12:00:00+0530
• 2016-12-19T06:30:00:000+00:00
  • or a rarer 2016-12-19T12:00:00:000+0000
  • 2016-12-19T06:30:00+00:00
  • or a rarer 2016-12-19T12:00:00+0000
• 2016-12-19T06:30:00:000Z
• 2016-12-19T06:30:00Z

Date-Time in node.js

Well... instead of trying to write it in words, I think the following node shell interaction can represent it better.

> var moment = require('moment');
undefined

> var date1 = Date();
undefined

> date1
'Mon Dec 19 2016 14:37:46 GMT+0530 (IST)'

> typeof date1
'string'

// So basically Date() just returns a string which is human compatible representation of date-time.

> var date2 = new Date();
undefined

> date2
2016-12-19T09:07:53.242Z

typeof date2
'object'

> date2.toString()
'Mon Dec 19 2016 14:37:53 GMT+0530 (IST)'
// a string with human compatible representation of the date in local timezone
// ok... so Date() is actually just (new Date()).toString()

// Also, when we concat a date to any other string, what we will get is date.toString()

> "test -- " + date2
'test -- Mon Dec 19 2016 14:37:53 GMT+0530 (IST)'

// a string with human readble represntaion in UTC/GMT

> date2.toGMTString()
'Mon, 19 Dec 2016 09:07:53 GMT'

> date2.toUTCString()
'Mon, 19 Dec 2016 09:07:53 GMT'

// Get the ISO 8601 representaion
// node Date's to ISO representation is actually with timzezone descriptor `Z`

> date2.toISOString()
'2016-12-19T09:07:53.242Z'

Enters Moment.js

// Never parse a date with moment without providing timezone
// A date-time representaion is incomplete without timezone information
var parsedDateBad = moment('2016-12-19', 'YYYY-MM-DD')

// Remember to always provide the date-time's timezone while parsing. ( add Z to format_string)

// the date was in UTC timezone
> var parsedDate1 = moment('2016-12-19 Z', 'YYYY-MM-DD Z')

> parsedDate1.toString()
'Mon Dec 19 2016 05:30:00 GMT+0530'

> parsedDate1.toISOString()
'2016-12-19T00:00:00.000Z'

// in date was in IST timezone
var parsedDate2 = moment('2016-12-19 +05:30', 'YYYY-MM-DD Z')

> parsedDate2.toString()
'Mon Dec 19 2016 00:00:00 GMT+0530'

> parsedDate2.toISOString()
'2016-12-18T18:30:00.000Z'

Postgres and date-time's

So, lets consider the three date-time related column types in Postgres.

• DATE
• TIMESTAMP
• TIMESTAMPTZ or TIMESTAMP WITH TIMEZONE

So... we are going to run our node.js and other clients on a server with timezone IST.

Now lets check the timezone for our postgres server.

SELECT  current_setting('TIMEZONE')

We get,

current_setting
UTC

So, our postgres server thinks of dates in UTC.

Now... lets create a table with all three of these column types,

CREATE TABLE date_time_test(
  id BIGINT,
  date_column DATE,
  timestamp_column TIMESTAMP,
  timestamptz_column TIMESTAMPTZ
);

Now lets insert the same time instant in timezone IST and UTC represented in ISO_8601 format

INSERT INTO
    date_time_test (id, date_column, timestamp_column, timestamptz_column)
    VALUES
        (1, '2016-12-19T12:00:00.000Z', '2016-12-19T12:00:00.000Z', '2016-12-19T12:00:00.000Z'),
        (2, '2016-12-19T17:30:00.000+05:30', '2016-12-19T17:30:00.000+05:30', '2016-12-19T17:30:00.000+05:30')
RETURNING *

And you will get following,

id	date_column	timestamp_column	timestamptz_column
1	2016-12-19	2016-12-19 12:00:00	2016-12-19 12:00:00
2	2016-12-19	2016-12-19 17:30:00	2016-12-19 12:00:00

Notice the difference between timestamp_column and timestamptz_column.

timestamp_column does not work with dates in timezones other than UTC.

** TIMESTAMP columns totally ignore the timezone descipter in date-string and just assume the date to be in the postgres server's timezone **

And here lies the problem with TIMESTAMP columns. You will have to insert your date-times with the same timezone as your postgres serever otherwise since postgres likes to think of date-times in its own timezone, it will end up with a different than intended timestamp.

Which means, if you are using TIMESTAMP columns, your source code has to be coupled with timezone of your server. Also... it is possible to make a mistake and send a bad date to postgres.

Try reading date-times from postgres with Node.js

function readTest1() {
  var query =
    `
    SELECT * FROM date_time_test
    `;
  console.log(query);

  return pgPool.execQuery(query)
    .then(function (result) {
      console.log(result.rows);
    });
}

readTest1();

And we get the following output (all are ISO_8601 UTC representations),

[

  {
    id: '1',
    date_column: 2016-12-18T18:30:00.000Z,
    timestamp_column: 2016-12-19T06:30:00.000Z,
    timestamptz_column: 2016-12-19T12:00:00.000Z
  },
  {
    id: '2',
    date_column: 2016-12-18T18:30:00.000Z,
    timestamp_column: 2016-12-19T12:00:00.000Z,
    timestamptz_column: 2016-12-19T12:00:00.000Z 
  } 

And... something strange happened with our TIMESTAMP + pg Node.js driver + IST client server combo. Note that postgres thinks these timestamps are UTC, but our pg driver is doing something strange and reading 2016-12-19T12:00:00.000Z as 2016-12-19T06:30:00.000Z which is very bad for us.

But TIMESTAMPTZ works as expected irrespective of timezone of our node client server.

So... ** We need TIMESTAMPTZ columns **

Try reading timestamptz with Node.js

function readTest1() {
  var query =
    `
    SELECT * FROM date_time_test
    `;
  console.log(query);

  return pgPool.execQuery(query)
    .then(function (result) {
     
      // No need to worry... the pg node driver returns the proper js date object
      // so you don't need to worry about timezone
      
      var timestamptz = result.rows[0].timestamptz_column;
      
      return { timestamptz: timestamptz };
    })
    .then(function (dates) {
      var mtstz = moment(timestamptz);
      
      // for utc demographic
      var utcTimeString = mtstz.utc().toString();
      //'Mon Dec 19 2016 12:00:00 GMT+0000'
      
      // for ist demographic
      var istTimeString = mtstz.utcOffset('+05:30').toString();
      //'Mon Dec 19 2016 17:30:00 GMT+0530'
    });
}

readTest1();

Write timestampz with Node.js

var moment = require('moment');

// lets say you have a date string
var dateTimeString1 =  '2016-12-20 12:00:00'
// but which timezone ? Date-Times are incomplete withtout the mention of timezone
// let say we know that this timestring came from a bank file and thus is IST.
// so now lets add the timezone desciptor to our dateTimeString
dateTimeString1 = dateTimeString1 + ' +05:30'
// now we can use the format `YYYY-MM-DD hh:mm:ss Z` to parse this
var date1 = moment(dateTimeString1, 'YYYY-MM-DD hh:mm:ss Z');

sqlQuery1 = `INSERT INTO date_time_test(timestamptz_column) VALUES ('${date1.toISOString()}')`;

// let say you had a date object...
var date2 = ...
sqlQuery2 = `INSERT INTO date_time_test(timestamptz_column) VALUES ('${date2.toISOString()}')`;

The thing is that Node.js pg is "luckily" able to avoid TIMESTAMP confusion with insertion of js date objects when postgres server timezone is UTC. As it internally uses date.toISOString() on js date objects, which gives the ISO_8601 string in UTC.

But if your server is in any other timezone, then this "lucky" co-incidence can hurt a lot. That is another reason to avoid TIMESTAMP in db.

And as for TIMESTAMPTZ, that works as expected for any case.

Reading timestamptz in sql queries,

So... by default postgres will reply back in its own timezone, but if you want to have more understandable dates, then just provide the desired timezone in your query itself.

If you need list of timezones then - https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

SELECT
    timestamptz_column AS DEFAULT,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'UTC' AS utc,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'Asia/Calcutta' AS india,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'Australia/Sydney' AS sydney
FROM date_time_test;

default	utc	india	sydney
2016-12-19 12:00:00	2016-12-19 12:00:00	2016-12-19 17:30:00	2016-12-19 23:00:00
2016-12-19 12:00:00	2016-12-19 12:00:00	2016-12-19 17:30:00	2016-12-19 23:00:00

Querying based on timestampz

So... since postgres likes to think according to its timezone... when you are querying... you have to take care of that,

So... with a query like following,

SELECT '2016-12-19T12:00:00'::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'Asia/Calcutta' AS timestamp_value;

timestamp_value
2016-12-19 12:00:00

So... when we don't mention timezone in our datestring, postgres thinks that you want to pick a UTC 2016-12-19 12:00:00 and convert it to timezone Asia/Calcutta.

So... when you want to query for IST 2016-12-19T12:00:00, you can either choose,

to provide IST timezone offset

SELECT '2016-12-19T12:00:00+05:30'::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'Asia/Calcutta' AS timestamp_value;

Or, use the corresponding UTC time,

SELECT '2016-12-19T06:30:00'::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'Asia/Calcutta' AS timestamp_value;

Similarly if you want to use IST time in a where query, you can either provide offset or use the corresponding UTC time

SELECT
    timestamptz_column AS default,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'UTC' AS utc,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'Asia/Calcutta' AS india,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'Australia/Sydney' AS sydney
FROM
    date_time_test
WHERE
    timestamptz_column = '2016-12-19T17:30:00+05:30'

default	utc	india	sydney
2016-12-19 12:00:00	2016-12-19 12:00:00	2016-12-19 17:30:00	2016-12-19 23:00:00
2016-12-19 12:00:00	2016-12-19 12:00:00	2016-12-19 17:30:00	2016-12-19 23:00:00

As for many reporting purposes... you may want to select records in a IST time range

lets say you want all record of date 2016-12-19 IST,

SELECT
    id,
    timestamptz_column AS default,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'UTC' AS utc,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'Asia/Calcutta' AS india
FROM
    date_time_test
WHERE
    timestamptz_column >= '2016-12-19T00:00:00+05:30'
    AND timestamptz_column < '2016-12-20T00:00:00+05:30'

id	default	utc	india
1	2016-12-19 12:00:00	2016-12-19 12:00:00	2016-12-19 17:30:00
2	2016-12-19 12:00:00	2016-12-19 12:00:00	2016-12-19 17:30:00

So... we need to migrate all TIMESTAMP's to TIMESTAMPTZ and edit our reporting queries to avoid all these problems.

How to work with TIMESTAMPTZ in a proper way

Change all TIMESTAMP columns to TIMESTAMPTZ

CREATE TABLE demo(
  id BIGINT,
  timestamptz_column TIMESTAMPTZ
);

Writing to postgres with Node.js

If you are doing anything similar to,

// you have a date object
var date = new Date();
var query =
  `
  INSERT
      INTO demo(id, timestamptz_column)
  VALUES ('1', '${date.toISOString()}');
  `;

Then you don't have to change anything... In other cases just ensure that you are providing an ISO_8601 date-time-string.

And base_resource implementaions handle date objects in a similar way, so no need to change in those cases.

Reading from postgres with Node.js

The node.js pg driver, converts timestamptz columns to js date objects without any problems to no need to change those.

Just remember that those date objects will have utc dates... so use moment to offset them for Indian dempgraphic whenever needed.

Querying from postgres with Node.js

The node.js pg driver can use date objects in queries without any problems to no need to change those.

And base_resource implementaions handle date objects in a similar way, so no need to change in those cases. Just check that your dates are as expected.

Writing to postgres with SQL

Make sure that the dates that you provide are proper ISO 8601 date-time-string with timezone descriptor.

INSERT INTO
    demo (id, timestamptz_column)
    VALUES
        (1, '2016-12-19T12:00:00.000Z'),
        (2, '2016-12-19T17:30:00.000+05:30')
RETURNING *

Reading from postgres with SQL

By default postgres will return date-times correspoding to its timezone which is UTC here. So... In case you want to see datetimes in a different timezone, you project those to your desired timezone by specifying it.

SELECT
    id,
    timestamptz_column AS default,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'UTC' AS utc,
    timestamptz_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'Asia/Calcutta' AS india
FROM demo;

Querying based on date-times with SQL,

for a particular timestamp,

SELECT
    *
FROM
    from
WHERE
    timestamptz_column = '2016-12-19T17:30:00+05:30'

for a time interval,

SELECT
    *
FROM
    demo
WHERE
    timestamptz_column >= '2016-12-19T00:00:00+05:30'
    AND timestamptz_column < '2016-12-20T00:00:00+05:30'