I have table of companies where each company has individual timezone. for example - company 1 has time zone UTC+10 and company 2 - UTC+2
table companies has field time_zone and stored abbreviation of zone like America/Los_Angeles(I can add additional field for store offset value from UTC if need).
and has table requests with start_date field where stored TIMESTAMP without time zone(UTC-0) for example -
id | company_id | start_date (utc-0)
------------------------------------
1 | 1 | 21-03-16 02-00 // added for company `21-03-16 12-00`
2 | 2 | 21-03-16 23-00 // added for company `22-03-16 01-00`
3 | 1 | 20-03-16 13-00 // added for company `20-03-16 23-00`
4 | 1 | 21-03-16 23-00 // added for company `22-03-16 09-00
I want select records that started from 21-03-16 00-00 to 21-03-16 23-59 considering time zone each company.
but if I will use -
select * from request where start_date between '2016-03-21 00:00:00.000000' AND '2016-03-21 23:59:59.999999'
I get requests where id = 2 and 4.
but these requests were added 22-03-16 by fact for each company.
Any suggestions how I can decide this situation by one select? Many thanks.
I'm not sure if I understand your question right, you might need to clarify.
But I'd say that joining with companies where the time zone information is stored should solve the problem:
SELECT r.*
FROM request r
JOIN companies c ON c.id = r.company_id
WHERE r.start_date BETWEEN '2016-03-21 00:00:00.000000'
AT TIME ZONE c.time_zone
AND '2016-03-21 23:59:59.999999'
AT TIME ZONE c.time_zone;