Get "time with time zone" from "time without time zone" and the time zone name

WARNING: PostgreSQL newbie (see comments on the question!). I know a bit about time zones though, so I know what makes sense to ask.

It looks to me like this is basically an unsupported situation (unfortunately) when it comes to AT TIME ZONE. Looking at the AT TIME ZONE documentation it gives a table where the "input" value types are only:

• timestamp without time zone
• timestamp with time zone
• time with time zone

We're missing the one you want: time without time zone. What you're asking is somewhat logical, although it does depend on the date... as different time zones can have different offsets depending on the date. For example, 12:00:00 Europe/London may mean 12:00:00 UTC, or it may mean 11:00:00 UTC, depending on whether it's winter or summer.

On my system, having set the system time zone to America/Regina, the query

SELECT ('2011-11-22T12:00:00'::TIMESTAMP WITHOUT TIME ZONE)                                AT TIME ZONE 'America/Vancouver'

gives me 2011-11-22 14:00:00-06 as a result. That's not ideal, but it does at least give the instant point in time (I think). I believe that if you fetched that with a client library - or compared it with another TIMESTAMP WITH TIME ZONE - you'd get the right result. It's just the text conversion that then uses the system time zone for output.

Would that be good enough for you? Can you either change your SCHEDULES.time field to be a TIMESTAMP WITHOUT TIME ZONE field, or (at query time) combine the time from the field with a date to create a timestamp without time zone?

EDIT: If you're happy with the "current date" it looks like you can just change your query to:

SELECT (current_date + SCHEDULES.time) AT TIME ZONE USERS.tzfrom SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

Of course, the current system date may not be the same as the current date in the local time zone. I think this will fix that part...

SELECT ((current_timestamp AT TIME ZONE USERS.tz)::DATE + schedules.time)       AT TIME ZONE USERS.tzfrom SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

In other words:

• Take the current instant
• Work out the local date/time in the user's time zone
• Take the date of that
• Add the schedule time to that date to get a TIMESTAMP WITHOUT TIME ZONE
• Use AT TIME ZONE to apply the time zone to that local date/time

I'm sure there's a better way, but I think it makes sense.

You should be aware that in some cases this could fail though:

• What do you want the result to be for a time of 01:30 on a day when the clock skips from 01:00 to 02:00, so 01:30 doesn't occur at all?
• What do you want the result to be for a time of 01:30 on a day when the clock goes back from 02:00 to 01:00, so 01:30 occurs twice?

Here is a demo how to calculate the times without casting to text:

CREATE TEMP TABLE schedule(t time, tz text);INSERT INTO schedule values ('12:00:00', 'America/Vancouver'),('12:00:00', 'US/Mountain'),('12:00:00', 'America/Regina');SELECT s.t AT TIME ZONE s.tz        - p.utc_offset        + EXTRACT (timezone from now()) * interval '1s'FROM   schedule sJOIN   pg_timezone_names p ON s.tz = p.name;

Basically you have to subtract the UTC offset and add the offset of your local time zone to arrive at the given time zone.

You can speed up the calculation by hardcoding your local offset. In your case (America/Regina) that should be:

SELECT s.t AT TIME ZONE s.tz        - p.utc_offset        - interval '6h'FROM   schedule sJOIN   pg_timezone_names p ON s.tz = p.name;

As pg_timezone_names is a view and not actually a system table, it is rather slow - just like the demonstrated variant with casting to text representation and back.

I would store the time zone abbreviations and take the double cast via text without joining in pg_timezone_names for optimum performance.

FAST solution

The culprit that's slowing you down is pg_timezone_names. After some testing I found that pg_timezone_abbrevs is far superior. Of course, you have to save correct time zone abbreviations instead of time zone names to achieve this. Time zone names take DST into consideration automatically, time zone abbreviations are basically just codes for a time offset. The documentation:

A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well.

Have a look at these test results or try yourself:

SELECT * FROM  pg_timezone_names;

Total runtime: 541.007 ms

SELECT * FROM pg_timezone_abbrevs;

Total runtime: 0.523 ms

Factor 1000. Whether you go with your idea to cast to text and back to timetz or with my method to compute the time is not important. Both methods are very fast. Just don't use pg_timezone_names.

Actually, as soon as you save time zone abbreviations, you can take the casting route without any additional joins. Use the abbreviation instead of the utc_offset. Results are accurate as per your definition.

CREATE TEMP TABLE schedule(t time, abbrev text);INSERT INTO schedule values ('12:00:00', 'PST')  -- 'America/Vancouver',('12:00:00', 'MST')  -- 'US/Mountain',('12:00:00', 'CST'); -- 'America/Regina'-- calculatingSELECT s.t AT TIME ZONE s.abbrev     - a.utc_offset     + EXTRACT (timezone from now()) * interval '1s'FROM   schedule sJOIN   pg_timezone_abbrevs a USING (abbrev);-- casting (even faster!)SELECT (t::text || abbrev)::timetzFROM   schedule s;