CODEWITHSUNDEEP
postgresql
user3341576
user3341576

Reputation: 191

How to get the EPOCH of a concatenated timestamp in PostgreSQL

In PostgreSQL, how do I get the epoch (UTC time) of a table date field whose time zone is assumed to be GMT and whose hours, min, seconds value is assumed to be '00:00:00'?

e.g. mytable.DT_GMT has values like '2018-10-16'.

Here's what I've tried so far. Instead of DT_GMT, I've just used '2018-10-16'. I'll replace it with the table field once I have some thing that works.

postgres=> select extract(epoch from TIMESTAMP WITH TIME ZONE concat('2018-10-16',' 00:00:00.00-00') );
ERROR:  syntax error at or near "concat"
LINE 1: ...elect extract(epoch from TIMESTAMP WITH TIME ZONE concat('20...

Trying something related:

postgres=> SELECT TIMESTAMP ('2018-10-16' || ' 00:00:00.00') AT TIME ZONE 'GMT';
ERROR:  syntax error at or near "'2018-10-16'"
LINE 1: SELECT TIMESTAMP ('2018-10-16' || ' 20:38:40') AT TIME ZONE ...

Note that the hours...sec is necessary because without it those values are not what's required.

postgres=> SELECT TIMESTAMP '2018-10-16' AT TIME ZONE 'GMT';
        timezone
------------------------
 2018-10-15 17:00:00-07
(1 row)

Upvotes: 0

Views: 337

Answers (1)

Schwern
Schwern

Reputation: 164659

Instead of a string concatenation, you add them as date and time with time zone.

test=> select extract(epoch from date '2018-10-16' + time with time zone '00:00:00.00-00');
 date_part  
------------
 1539648000
(1 row)

If you're only adding '00:00:00' to make the time zones work, you don't need it with a date.

test=> select date_part('epoch', date '2018-10-16');
 date_part  
------------
 1539648000

$ date --date '@1539648000' --utc
Tue Oct 16 00:00:00 UTC 2018

This is a good reason to store dates and times as their proper type, especially in Postgres which cares a lot about types, rather than as text.

Upvotes: 3

Related Questions