Javascript datetime to and from postgres with time zone

First of all, shout out to this blog that goes much deeper into this and is how I learned this concept:

https://blog.untrod.com/2016/08/actually-understanding-timezones-in-postgresql.html

Problem:

Inserting a datetime from js to postgres and then selecting that same time back out will likely end up with the wrong timezone on your select statement unless you know what you are doing. This happens even if you are inserting UTC and attempting to select UTC.

Specifically what happened to me and the blog post above is instead of getting UTC back, I got UTC + 2 x my UTC offset, which is very very wrong.

Bad solution:

• Use AT TIME ZONE 'UTC' in select statements. Ex: SELECT log_time AT TIME ZONE 'UTC' as log_time_utc FROM logs

This is bad because you will have to do this everywhere you use log_time. Lets find a better solution that just works as expected.

Solution:

• Use timestamptz as your database column definition (which is timezone aware, vs just timestamp which is not)
• Put a timezone offset string at the end of your datetime value prior to inserting, otherwise postgres won't understand what is being inserted (even for UTC you must put a "+0")

Example:
In my javascript helper utilities before inserting a new date, which are created with local time, I have been converting to UTC time.

function makeDateTimeNowForDbUtc(inDate){

if( inDate === undefined ){
  var inDate = new Date;
}
var outDate = inDate.getUTCFullYear() + '-' +
('00' + (inDate.getUTCMonth()+1)).slice(-2) + '-' +
('00' + (inDate.getUTCDate()).slice(-2) + ' ' +
('00' + (inDate.getHours()).slice(-2) ':' +
('00' + (inDate.getUTCMinutes()).slice(-2) + ':' + 
('00' + (inDate.getUTCSeconds()).slice(-2) + '.' +
('00' + (inDate.getUTCMilliseconds()).slice(-3);

outDate = outDate + "+0"; // addition for postgres
return outDate;

Now I don't have to worry about it and my dates go in as UTC and back out as UTC without any additional code!

NOTE: if you are getting confused by the time zone offset in the javascript date time use toUTCString to get straight UTC time from your date.

let dt = myPostgresSelectReturn();
console.log('UTC Value: ' + dt.toUTCString()); // Date time GMT where GMT = UTC

Hope this helps!