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

Question

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.

Answer 1

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:

• Time zone storage in data type "timestamp with time zone"

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:

• https://www.postgresql.org/docs/current/view-pg-timezone-names.html
• Ignoring time zones altogether in Rails and PostgreSQL

Answer 2

This is a conversion to timestamp with time zone. You can format it to whatever text representation you wish. My timezone is East European Time, EET (GMT + 2), so

select to_date('20211203', 'YYYYMMDD')::timestamp at time zone 'EET';
-- 2021-12-03 00:00:00.000 +0200

Answer 3

The question seems to assume that timestamps can be formatted on write and that they retain time zone information. They can't and they don't, which splits the problem in two:

1: How to interpret text-based timestamps.

create table test_timestamps as 
select ('20211203'||'Japan')::timestamptz as "your_timestamp";

PostgreSQL saves that in binary format in UTC so the entire idea of formatting and time zone doesn't apply to a stored timestamp type data. It's only formatted on read and converted to a different time zone if required.

2: How to get them in a specific format on read.

select to_char(
         "your_timestamp" AT TIME ZONE 'UTC', 
         'YYYY-MM-DD HH24:MI:SS.US TZHTZM') 
from test_timestamps;

where at time zone is only necessary if you want it to return the timestamp as observed elsewhere.

Answer 4

To keep from over complicating things:

create table dt_test (id integer, ts_fld timestamp, tsz_fld timestamp with time zone);

insert into dt_test values (1, '20211203'::date, '20211203'::date);

 select * from dt_test ;
 id |       ts_fld        |        tsz_fld         
----+---------------------+------------------------
  1 | 2021-12-03 00:00:00 | 2021-12-03 00:00:00-08

A date will be taken as Midnight for timestamp purposes. So either just use it as date knowing it will be Midnight or do the explicit cast:

select '20211203'::date::timestamp, '20211203'::date::timestamptz;
      timestamp      |      timestamptz       
---------------------+------------------------
 2021-12-03 00:00:00 | 2021-12-03 00:00:00-08

--Which can be shortened to:

select '20211203'::timestamp, '20211203'::timestamptz;
      timestamp      |      timestamptz       
---------------------+------------------------
 2021-12-03 00:00:00 | 2021-12-03 00:00:00-08

Depending on whether you want to retain the time zone offset or not. For portability 2021-12-03 00:00:00-08 would be the better choice.