How to convert local time to UTC?

Question

I need to convert local time to UTC using a function. The inputs I have is the local time, timezone of the local time (e.g. 'Pacific/Auckland'). What I need to get from the procedure is the UTC time for the local time based on given timezone.

Can someone please help?

I am using version 8.3

Answer 1

This is covered in the manual, but it's not always obvious how to actually work with dates/times. The SQL spec is a bit bizarre.

In the case of your question it isn't clear whether you want to store the time in UTC but display it in the server's local time (TimeZone), or whether you wan to ignore TimeZone and always display it as UTC. I'll assume the latter.

For timestamps you'd just use AT TIME ZONE twice, like:

SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'Australia/Sydney' AT TIME ZONE 'UTC';

You need to use AT TIME ZONE twice. Once to convert the input timestamp to timestamptz according to the argument timezone, then another to convert that to a timestamp at UTC.

Unfortunately because of the (IMO insane) way the SQL spec defines AT TIME ZONE for TIME, you can't do the same thing for TIME. You'll have to manipulate the TimeZone variable instead:

SET TimeZone = 'UTC';
SELECT TIME '07:00' AT TIME ZONE 'Australia/Sydney';

This still leaves you with a timetz not a time. So its display value changes with the timezone setting.