CODEWITHSUNDEEP
datetimeamazon-redshift
Swapniel
Swapniel

Reputation: 183

How to combine date and time into timestamp?

I have the column d_date having values like "2017-04-01 00:00:00" and column t_time having values like "1970-01-01 09:43:35" and I want to combine it to one time stamp like "2017-04-01 09:43:35".

Both of the input columns are of type timestamp.

Please help.

Upvotes: 3

Views: 5216

Answers (3)

jydiw
jydiw

Reputation: 41

You can now concatenate DATE and TIME(TZ) into TIMESTAMP(TZ):

https://docs.aws.amazon.com/redshift/latest/dg/r_DATE-CONCATENATE_function.html

SELECT
  date + time,            -- TIMESTAMP
  date + timetz,          -- TIMESTAMPTZ
  datetime::DATE + time   -- TIMESTAMP
FROM
  table

Upvotes: 1

Rockin4Life33
Rockin4Life33

Reputation: 2405

What about using the TIMESTAMP(date, time) built in function?

SELECT TIMESTAMP(entry_date, entry_time) AS timestamp
...

Upvotes: 1

Paulo Scardine
Paulo Scardine

Reputation: 77399

I know nothing about redshift but based on the docs I would try something like this:

SELECT
  DATEADD(second, 
          DATEDIFF(seconds, d_time, DATE_TRUNC('day', d_time)), 
          t_date) as dt_datetime
...

I'm not going to setup a redshift database just to test the suggestion above but I'm willing to help you over chat if the above does not work.

Upvotes: 0

Related Questions