How do I prevent Redshift INSERT datetime from dropping the timezone?

Question

I have a String in this format: 2018-11-01T00:00:00-07:00 and I would like to convert it to a TIMESTAMP and insert it into a TIMESTAMP column. However, when I insert it, it drops the -07:00 without first converting it to -00:00. How do I ensure that it is converted and stored in Redshift properly?

Here is an example: select ORIGINAL_DATE, TO_TIMESTAMP(ORIGINAL_DATE,'YYYY-MM-DD HH24:MI:SS') FROM CDW_LANDING.X where id = XXXXXX;

=> 2018-11-01T00:00:00-07:00 2018-10-31 17:00:00

The TO_TIMESTAMP converts it to 2018-10-31 17:00:00 which is what I want. However, when I insert it, it becomes 2018-11-01 00:00:00 and simply drops the -07:00.

Here is the example:

insert into cdw_stage.X (ORIG_DT) 
select TO_TIMESTAMP(ORIGINAL_DATE,'YYYY-MM-DD HH24:MI:SS') 
from CDW_LANDING.INVOICE where id = XXXXXX;

But when I query it with select ORIG_DT from cdw_landing.X;, it displays 2018-11-01 00:00:00. What I would like to see is 2018-10-31 17:00:00 which is what the TO_TIMESTAMP function should do.

The ORIG_DT in Redshift is in TIMESTAMP format. The input date is in VARCHAR.

How do I get Redshift to save this correctly? I also added postgres tag because Redshift is based off of postgres. Thank you so much!!!

related: https://stackoverflow.com/questions/54897691/representing-a-future-time-in-postgresql — Daniel A. White, Mar 05 '19 at 01:37

Erwin Brandstetter · Accepted Answer · 2019-03-06T00:31:55.303

2018-11-01T00:00:00-07:00 is not a timestamp (timestamp without time zone) literal, strictly speaking. It is a timestamptz (timestamp with time zone) literal. This is the root of all pain in your question. The wrong cast to timestamp ignores the offset. The Postgres manual:

In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.

Bold emphasis mine.

The use of TO_TIMESTAMP() can't save you. The Redshift manual:

Formats that include a time zone (TZ, tz, or OF) are not supported as input.

(The same is true in Postgres.)

Solution

Cast to timestamptz (or use a column of that type to begin with), the rest should fall in place:

SELECT cast('2018-11-01T00:00:00-07:00' AS timestamptz);

Or:

SELECT '2018-11-01T00:00:00-07:00'::timestamptz;

The manual about casting in Redshift.

When an actual timestamptz is assigned to a timestamp column it is converted according to the current timezone setting of the session automatically. If you want a different target timezone, use the AT TIME ZONE construct. Details:

Ignoring time zones altogether in Rails and PostgreSQL

The related answer is for Postgres, but timestamp handling in Redshift (while differing in many other aspects!) is the same. The Redshift manual:

When converting DATE or TIMESTAMP to TIMESTAMPTZ, DATE or TIMESTAMP are assumed to use the current session time zone. The session time zone is UTC by default. For more information about setting the session time zone, see timezone.

Those SQL commands does not work in Redshift but this leads me to the correct answer. The correct solution is to set the format in Redshift as `TIMESTAMPTZ` instead of `TIMESTAMP` and everything works without modification. — oky_sabeni, Mar 05 '19 at 23:08
@okysabeni: Cool. The cast should work in Redshift, too. I had a stray `\`` character in the string, that may have mislead you. Added some more. — Erwin Brandstetter, Mar 06 '19 at 00:37

How do I prevent Redshift INSERT datetime from dropping the timezone?

1 Answers1

Solution