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

Question

First off, I realize time with time zone is not recommended. I am going to use it because I'm comparing multiple time with time zone values to my current system time regardless of day. I.e. a user says start everyday at 08:00 and finish at 12:00 with THEIR time zone, not the system time zone. So, I have a time without time zone column in one table, let's call it SCHEDULES.time and I have a UNIX time zone name column in another table, let's call it USERS.tz.

My system time zone is 'America/Regina', which does not use DST and so the offset is always -06.

Given a time of '12:00:00' and a tz of 'America/Vancouver' I would like to select the data into a column of type time with time zone but I DO NOT want to convert the time to my time zone because the user has effectively said begin at when it is 12:00 in Vancouver, not in Regina.

Thus, doing:

SELECT SCHEDULES.time AT TIME ZONE USERS.tz FROM SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID; 

results (at the moment) in:

'10:00:00-08' 

but I really want:

'12:00:00-08' 

I can't find any documentation relating to applying a time zone to a time, other then AT TIME ZONE. Is there a way to accomplish this without character manipulation or other hacks?

UPDATE: This can be accomplished by using string concatenation, casting, and the Postgres time zone view as such:

select ('12:00:00'::text || utc_offset::text)::timetz from pg_timezone_names where name = 'America/Vancouver'; 

However, this is fairly slow. There must be a better way, no?

UPDATE 2: I apologize for the confusion. The SCHEDULES table DOES NOT use time with time zone, I am trying to SELECT a time with time zone by combining values from a time without time zone and a text time zone name.

UPDATE 3: Thanks to all those involved for their (heated) discussion. :) I have been convinced to abandon my plan to use a time with time zone for my output and instead use a timestamp with time zone as it performs well, is more readable, and solves another problem that I was going to run into, time zones that roll into new dates. IE. '2011-11-21 23:59' in 'America/Vancouver' is '2011-11-22' in 'America/Regina'.

UPDATE 4: As I said in my last update, I have chosen the answer that @MichaelKrelin-hacker first proposed and @JonSkeet finalized. That is, a timestamp with time zone as my final output is a better solution. I ended up using a query like:

SELECT timezone(USERS.tz, now()::date + SCHEDULES.time) FROM SCHEDULES JOIN USERS ON USERS.ID = SCHEDULES.USERID; 

The timezone() format was rewritten by Postgres after I entered (current_date + SCHEDULES.time) AT TIME ZONE USERS.tz into my view.

Answer 1

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.tz from 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.tz from 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?