CODEWITHSUNDEEP
postgresqldatetimetimetimezonepostgresql-9.4
David Chouinard
David Chouinard

Reputation: 6836

Determining whether a UTC timestamp is within a time range in a timezone?

Let's frame this problem as a scheduling application. Users specify a minimum time in their timezone ("8am" in "America/New_York") after which they're willing to take appointments. (we also have a similar constraint on the maximum time)

Given an incoming appointment request (say, for 2016-09-07 03:00:00 in UTC time), we need to determine which users can take the appointment.

How can we determine whether a UTC timestamp is between two timezone-adjusted times?

My approach has been to convert the incoming timestamp to the user's timezone, extract the time portion and compare it to the time constraints:

SELECT * FROM users u WHERE
   ('2016-09-07 03:00:00' AT TIME ZONE u.timezone)::time >= u.earliest_start_time AND
   ('2016-09-07 03:00:00' AT TIME ZONE u.timezone)::time <= u.latest_stop_time

(earliest_start_time and latest_stop_time are of type time without time zone; timezone is an Olson tz string)

There's all sorts of odd behavior as timezone offsets causes days to roll over and I'm just generally very confused and hazy about this.

Upvotes: 0

Views: 317

Answers (1)

redneb
redneb

Reputation: 23850

To make the code below more readable, suppose that the placeholder $1 holds a value of type timestamp with time zone. Then the following query will find all users that are available at that time:

SELECT * FROM users u WHERE
    $1 BETWEEN
      ($1::date || ' ' || u.earliest_start_time)::timestamp without time zone AT TIME ZONE u.timezone
    AND
      ($1::date || ' ' || u.latest_stop_time)::timestamp without time zone AT TIME ZONE u.timezone;

Explanation: we take $1 which is a timestamp with time zone and we keep only the date part by type casting it into a date. Then we take earliest_start_time which is a time and combine it with the previous date and we get full time stamp which doesn't have time zone information (yet). Then we specify the time zone to convert that into a timestamp with time zone. We do the same for latest_stop_time. So now that we have found those 2 timestamp with time zone values, we can just compare them with $1 which has the same type.

This will have problem with ambiguous timestamps though (i.e. in the transition from DST to standard time), but you cannot avoid that. If you only use it for typical businesses hours, it might be ok.

Upvotes: 2

Related Questions