Search code examples
postgresqldatetimezonesql-timestamp

Easiest way to convert YYYYMMDD to the midnight with specific timezone on PostgreSQL?

I get the dates in a YYYYMMDD format. I need to convert this column to the timestamp with specific timezone. What is the best/easiest way to do that?

So if I have 20210101 I want to get 2021-01-01 00:00:00.000000 in my TZ.

Solution

  • Strictly speaking, your request is an oxymoron:

    I want to get 2021-01-01 00:00:00.000000 in my TZ.

    You show a timestamp (timestamp without time zone) literal, which is completely orthogonal to (and ignorant of) the concept of time zones.

    But you want it "in my TZ", which would imply to a timestamptz (timestamp with time zone) value, where the corresponding literal includes a time offset like: 2021-01-01 00:00:00.000000+01.

    Since the format YYYYMMDD is unambiguous ISO format, you can cast to date or timestamp directly, safely. A cast to timestamp assumes the time component 00:00 automatically. Produces your desired timestamp '2021-01-01 00:00'.

    SELECT '20211203'::timestamp;

    If you want the result type timestamp, we are done here.

    If you want the result type timestamptz, there is a quick-and-dirty shortcut:

    SELECT '20211203'::timestamptz;

    The current time zone setting is assumed for the type cast. But this introduces a dependency on a runtime settings. Notoriously unreliable, only advisable for situations where you can be certain of the current setting ...

    The sure and generally advisable way is to define the target time zone with the AT TIME ZONE construct explicitly. Say, your timezone is 'Europe/Vienna':

    SELECT '20211203'::timestamp AT TIME ZONE 'Europe/Vienna';

    Use a time zone name. Time zone abbreviations are treacherous for input conversion and may fail for daylight saving time (DST) or other bureaucratic nonsense. 'CET' (Central European Time) is appropriate for timestamps during "standard time". During DST periods, you'd have to use 'CEST' (Central European Summer Time).

    DST is utter nonsense, but some countries, including the EU, still haven't managed to get rid of it.

    db<>fiddle here - note that dbfiddle runs with time zone GB by default.

    Now you have the timestamptz value representing the start of the day (00:00) in your given time zone. Don't be fooled by the display of timestamptz values. That's always adjusted to the current time zone setting, but it always represents that unique point in time, just with different ways to display it.

    You do understand that the time zone itself is never stored in a timestamptz value, right? Even though timestamp with time zone sounds like it might. See:

    To force a certain display use to_char() or some other functions to generate the desired string. There is a dedicated page Data Type Formatting Functions in the manual.

    Related: