Postgres query with time zone

Question

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.

Like request.start_date AT TIME ZONE companies.time_zone BETWEEN '2016-03-21 00:00:00Z' AND '2016-03-21 23:59:59.999999Z'? OFC this will only work, when every row have a PostgreSQL accepted time zone in time_zone. You can define a check constraint, like CHECK ((timestamp '2000-01-01 00:00:00' AT TIME ZONE time_zone) IS NOT NULL) to validate time_zone. — pozs
– pozs, Commented Mar 24, 2017 at 13:23

Laurenz Albe · Accepted Answer · 2017-03-24 13:22:47Z

6

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;

answered Mar 24, 2017 at 13:22

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgres query with time zone

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