Is there a simple and clean way to convert `DATE` to the `TIMESTAMP AT TIME ZONE` of that date at midnight UTC?
date, postgresql, sql, timezone
Solution
Your first approach is correct. And it's not that ugly, is it? In simplified Postgres syntax:
SELECT '2012-1-1'::date::timestamp AT TIME ZONE 'UTC';
Applied to a variable or column it looks even more elegant:
SELECT mydate::timestamp AT TIME ZONE 'UTC';
If you are going to enter the date manually, you can shortcut to:
SELECT '2012-1-1 0:0'::timestamp AT TIME ZONE 'UTC'
The result will always be displayed according to the local timezone of the client (i.e. with the according offset), but that has no influence on the value.
Problem
This should be really simple, but I'm having an embarrassing amount of trouble with it. In PostgreSQL 9.1, I need to intepret a field stored as a `DATE` in the DB as if it were a `TIMESTAMPTZ` representing midnight UTC on that date. I'd like to do it in a clean and readable way that someone can come along, look at, and understand what's happening. The only ways I've found to do it so far are both very ugly. One converts it to a `TIMESTAMP WITHOUT TIME ZONE` then creates a `TIMESTAMP WITH TIME ZONE` from it by interpreting it as if it were UTC: ``` SELECT CAST(DATE '2012-01-01' AS TIMESTAMP WITHOUT TIME ZONE) AT TIME ZONE 'utc' ``` The other way is worse: ``` ('2012-01-01'::date)::timestamptz - (current_timestamp AT TIME ZONE 'UTC' - current_timestamp) ``` in that it converts the date to a timestamp for midnight local time, then subtracts the time zone offset. I couldn't find any way to get that offset as an interval natively (which seems crazy) so I landed up getting it by comparing `current_timestamp` in local time with `current_timestamp` in UTC. The only other way I could work out used `extract` to get the date parts and assembled a new `timestamptz` from them. I won't even show that one, it's too ugly. Both approaches feel all kinds of weird and wrong. Is there any sane way - standard or no - to convert in a readable and easily understood way from a `DATE` to a `timestamptz` of midnight UTC on that date? I'm looking for something like (imaginary, won't work) ``` '2012-01-01'::date AS TIMESTAMPTZ IN TIME ZONE '00:00'; ``` or ``` to_timestamp('2012-01-01'::date, '00:00'::time, 'UTC'); ``` Please point out the stupidly obvious thing I'm missing. Note that I'm testing to make sure the date is truly right internally, not just at display, with `extract(epoch from $1)` where `$1` is the converted date.