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

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.

Thanks!

CodePudding user response：

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

CodePudding user response：

Since the format YYYYMMDD is unambiguous ISO format, you can cast to date or timestamp directly, safely:

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

But use a time zone name. Not an abbreviation. Else the conversion may fail for daylight saving time (DST) or other bureaucratic nonsense.

See:

CodePudding user response：

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:

How to correctly interpret text-based timestamps.
How to get these timestamps in a specific format on read.

1: @Erwin Brandstetter's answer

select '20211203'::timestamp at time zone 'Europe/Vienna';

interprets the timestamp according to the current show TimeZone; setting, then displays it as observed in Vienna. Given the same input timestamp, this will give you different outputs depending on where you are. You can see this already happening in the comments under the answer from @Stefanov.sm, whose solution does the same thing.

If we wanted to actually load a Vienna timestamp, it would have to be

create table test as select ('20211203'||'Europe/Vienna')::timestamptz as "correct_timestamp";

Using at time zone to save/write something isn't needed. 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: Second problem is covered by a simple

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

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

CodePudding user response：

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.