Postgres: get local timestamp with time zone of latest midnight
postgresql, timezone
Solution
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'
Problem
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?