CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshiftutc
Codutie
Codutie

Reputation: 1075

Redshift: how to create datetime field that automatically converts incoming data to 'utc'

I want to create a table in redshift that stores incrementally incoming data from the source. The date field in the mysql source is not stored as UTC. Is it possible to convert and store the new record as UTC upon record creation.

I was thinking doing something like that:

CREATE TABLE test(
  my_dt_field datetime without timezone NOT NULL ...)

Any help would be very appreciated!

Upvotes: 0

Views: 3559

Answers (1)

sardar
sardar

Reputation: 145

Redshift provides following options of datatypes available to store dates:

1.DATE Use the DATE data type to store simple calendar dates without time stamps.

2.TIMESTAMP TIMESTAMP is an alias of TIMESTAMP WITHOUT TIME ZONE.

Use the TIMESTAMP data type to store complete timestamp values that include the date and the time of day.

TIMESTAMP columns store values with up to a maximum of 6 digits of precision for fractional seconds.

If you insert a date into a TIMESTAMP column, or a date with a partial time stamp value, the value is implicitly converted into a full time stamp value with default values (00) for missing hours, minutes, and seconds. Time zone values in input strings are ignored.

By default, TIMESTAMP values are Coordinated Universal Time (UTC) in both user tables and Amazon Redshift system tables.

3.TIMESTAMPTZ TIMESTAMPTZ is an alias of TIMESTAMP WITH TIME ZONE.

Use the TIMESTAMPTZ data type to input complete time stamp values that include the date, the time of day, and a time zone. When an input value includes a time zone, Amazon Redshift uses the time zone to convert the value to Coordinated Universal Time (UTC) and stores the UTC value.

To view a list of supported time zone names, execute the following command. select pg_timezone_names();

To answer your question declare your column datatype as TIMESTAMP, by default it stores in UTC

You can also refer AWS document here: https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html

Upvotes: 2

Related Questions