CODEWITHSUNDEEP
postgresql
Stussa
Stussa

Reputation: 3415

Postgres SELECT Across Time Zones Specific Day

I have a two tables (orders and regions) that when joined return 

orders.date        | orders.regions.tz
--------------------------------------
2016-01-01 2:00:00 | PST
2016-01-01 2:00:00 | EST
2016-01-01 2:00:00 | EST
...

I can select my different times in the corresponding time zones using:

SELECT date::timestamp at time zone regions.tz 
FROM orders INNER JOIN regions ON orders.region_id = regions.id;

Now I'm trying to find a way to SELECT all orders on that are on a specific day. That is:

I'm guessing this is going to rely on some sort of use of without timezone and time but I'm unsure of how to proceed.

Upvotes: 1

Views: 470

Answers (2)

Kevin Sylvestre
Kevin Sylvestre

Reputation: 38012

Your best bet is to probably cast to ::date:

SELECT orders.*
FROM orders INNER JOIN regions ON orders.region_id = regions.id
WHERE (scheduled::timestamp at time zone regions.tz)::date = '2016-1-1'::date;

Upvotes: 1

n_b
n_b

Reputation: 1156

While I don't know if it is the most efficient way to do things, this could just be done with a bunch of OR satements.

SELECT date::timestamp at time zone regions.tz 
FROM orders INNER JOIN regions ON orders.region_id = regions.id
WHERE (date::timestamp BETWEEN startTime AND endTime AND regions.tz = 'PST')
OR (date::timestamp BETWEEN startTime AND endTime AND regions.tz = 'EST')

You'll of course have to figure out all the start and end times for each individual timezone.

Upvotes: 0

Related Questions