0

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) ?

mythicalcoder
  • 3,143
  • 1
  • 32
  • 42

1 Answers1

1

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)
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Does it take care of 24 hours format ? I think the query is converting my 24 hours clock to 12 hours !! – mythicalcoder May 04 '17 at 05:05
  • Thanks Vao. When I tried, it wasn't converting properly. Does it have anything to do with the system clock settings as well ? I will check with few examples to convince myself. – mythicalcoder May 04 '17 at 09:17
  • post samples?.. It's hard to tell before you see the exact error – Vao Tsun May 04 '17 at 09:21
  • `select replace('Tue Aug 30 2016 14:07:13 GMT+0530 (IST)','GMT+0530 (IST)','GMT+05:30')::timestamptz;` gives `2016-08-31 01:07:13+05:30`. I am in +0530 zone. Is this correct ? – mythicalcoder May 04 '17 at 10:40
  • 1
    updated the answer with way to check. also yo ucan set timezone for your client to avoid double conversion mess – Vao Tsun May 04 '17 at 11:11
  • Thanks. That solved the problem. I will have to store in timestamp without time zone format. If you can, plz try to look at this also http://stackoverflow.com/questions/43780737/log-specific-postgresql-query-using-pg-promise – mythicalcoder May 04 '17 at 11:21
  • 1
    ask @vitaly-t - he's quite active on SO – Vao Tsun May 04 '17 at 11:46