Accounting for timezone in postgres query

Question

In am trying to automatically account for timezone in my postgres query. My goal is to select records that are between 0:00 and 23:59 in EST or UTC - 5:00.

The current query only returns records between 0:00 and 23:59 in UTC time.

select path, start_time from routes where routes.network_id = 1 and routes.start_time between '2017-06-13 00:00:00'::timestamp AND '2017-06-13 23:59:59'::timestamp

the column start_time is a timestamp without timezone, so by default it is in UTC

SELECT pg_typeof("start_time") from routes limit 1;

returns timestamp without timezone

how would one write a query to account for 5 hours difference and convert start_time to UTC - 5?

this is in postgres

iskandarblue
– iskandarblue

2017-07-07 19:32:06 +00:00
Commented Jul 7, 2017 at 19:32 — iskandarblue
– iskandarblue, Commented Jul 7, 2017 at 19:32

rd_nielsen · Accepted Answer · 2017-07-07 19:53:49Z

2

Try this:

select path, start_time - interval '5 hours' as start_time_est
  from routes
 where routes.network_id = 1 
       and routes.start_time between '2017-06-13 00:00:00-5'::timestamp  with time zone 
           AND '2017-06-13 23:59:59-5'::timestamp with time zone;

answered Jul 7, 2017 at 19:53

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Accounting for timezone in postgres query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related