Postgres: get the local timestamp with the time zone of the last midnight

Question

I am in the time zone Europe / Berlin (+02), Postgresql works in UTC (+00). I need to get a timestamp with a time zone on the last local date of midnight (the start date of the day of the current day in my time zone).

So, my end result will 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 using

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

Unfortunately, this gives the wrong date (the previous day is midnight) at 00:00 and 02:00, since UTC is the previous day, and today, apparently, utc is used to calculate the rest.

If we have 2013-03-03 00:05 in 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 rather ugly.

Is there a cleaner version of this command?

Answer 1

Use timestamptz . tz at the end means with time zone :

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

Or if you like more:

 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;