KevMoe
KevMoe

Reputation: 443

PGSQL - How do I Add 5 hours to DATE in WHERE Clause?

I either need to add 5 hours or convert from GMT to EST. The return is currently showing everything from 7p and later yesterday...

WHERE
incident.initial_symptom = 'Chrome Upgrade' AND
DATE(incident.install_completed) = CURRENT_DATE;

Upvotes: 0

Views: 384

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51609

Instead of manually adding interval to get wanted time zone, use at time zone, eg:

t=# select now(), now() at time zone 'est';
             now              |         timezone
------------------------------+---------------------------
 2017-04-07 07:07:39.17234+00 | 2017-04-07 02:07:39.17234
(1 row)

Depending on your timezone, exactly same statement adding interval to your date gives different result, eg at DST shift hour:

t=# set timezone TO 'WET';
SET
t=# select '2017-03-26 00:00:00'::timestamptz + '1 hour'::interval;
        ?column?
------------------------
 2017-03-26 02:00:00+01
(1 row)

t=# set timezone TO 'EET';
SET
t=# select '2017-03-26 00:00:00'::timestamptz + '1 hour'::interval;
        ?column?
------------------------
 2017-03-26 01:00:00+02
(1 row)

Upvotes: 2

Related Questions