I have referred to the psql documentation and came up with this query.
SELECT to_timestamp('Tue Aug 30 2016 04:07:13 GMT+0530 (IST)', 'Dy MON DD YYYY HH24:MI:SS');
This date time string Tue Aug 30 2016 04:07:13 GMT+0530 (IST) is what I got from MongoDB printjson(createdAt).
The above postresql doesn't seem to work correctly for all offsets.
I tried this
select to_timestamp('Tue Aug 30 2016 04:07:13 GMT+0530 (IST)', 'Dy MON DD YYYY HH24:MI:SS "GMT"OF "(IST)"');
But I get this error ``ERROR: "TZ"/"tz"/"OF" format patterns are not supported in to_date`.
How to convert to psql timestamptz format from this string Tue Aug 30 2016 04:07:13 GMT+0530 (IST) ?
It looks an ugly wheel and requires such replace for each unrecognized offset, but it works. For your sample replace 'GMT+0530 (IST)' to 'GMT+05:30' and it will be picked up:
t=# select replace('Tue Aug 30 2016 04:07:13 GMT+0530 (IST)','GMT+0530 (IST)','GMT+05:30')::timestamptz;
replace
------------------------
2016-08-30 09:37:13+00
(1 row)
t=# select replace('Tue Aug 30 2016 14:07:13 GMT+0530 (IST)','GMT+0530 (IST)','GMT+05:30')::timestamptz;
replace
------------------------
2016-08-30 19:37:13+00
(1 row)
update: depending on your timezone result can be confusing:
t=# set timezone TO 'GMT-5:30'; SET
t=# select replace('Tue Aug 30 2016 14:07:13 GMT+0530 (IST)','GMT+0530 (IST)','GMT+05:30')::timestamptz;
replace
---------------------------
2016-08-31 01:07:13+05:30
(1 row)
to check if it is right, use:
t=# select replace('Tue Aug 30 2016 14:07:13 GMT+0530 (IST)','GMT+0530 (IST)','GMT+05:30')::timestamptz at time zone 'UTC';
timezone
---------------------
2016-08-30 19:37:13
(1 row)