Postgres: get local timestamp with time zone of latest midnight

Question

I'm in the Time Zone Europe/Berlin (+02), Postgresql is running at UTC (+00). I need to get the timestamp with time zone at the latest local midnight date (The start of day date of the current day in my time zone).

So my end result would be something like this if we have 2013-03-03 14:00:00+02

2013-03-03 22:00:00+00
2013-03-04 00:00:00+02 // the same

I tried to get this date with

SELECT TIMESTAMP 'today' AT TIME ZONE 'Europe/Berlin'

Unfortunately this yields the wrong date (the previous day midnight) during 00:00 and 02:00 as the UTC time is stil at the previous day and today seems to use utc to calculate the rest.

If we have 2013-03-03 00:05 at Europe/Berlin this will return

2013-05-01 22:00:00+00

If I want to have the correct date I need to use

SELECT date_trunc('day', now() AT TIME ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin';
2013-05-02 22:00:00+00

which is correct, but quite ugly.

Is there a cleaner variant of this command?

Answer 1

Use timestamptz. The tz at the end meaning with time zone:

SELECT TIMESTAMPTZ 'today' AT TIME ZONE 'Europe/Berlin'

Or if you like it more explicit:

SELECT TIMESTAMP with time zone 'today' AT TIME ZONE 'Europe/Berlin'

Answer 2

Wrap it in a function:

create function midnight() returns timestamptz as $$
  select date_trunc('day', now() AT TIME ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin';
$$ language sql;

Answer 3

Based on Erwin's answer to a related question, this was the simplest and fastest way I figured out how to do it:

SELECT timezone('Europe/Berlin', now()::date::timestamp) AS local_midnight_in_utc;

The key is the cast to a timestamp, which removes the time zone from the date.

You can test your sample time of '2013-03-03 00:05' with this:

SELECT timezone('Europe/Berlin', '2013-03-03 00:05'::date::timestamp) AS midnight;

and it returns

2013-03-02 23:00:00+00

According to explain analyze, this is about 3x as fast as the datetrunc version. A runtime of .017ms vs 0.006ms based on a best of 5 runs.