How to convert int (yyyymmdd) to timestamp in SQL?

1
votes

I am using Redshift and have a field in a table as type int representing a day's date. For example the int is 20180215 . The format is yyyymmdd.

I would like to know what is the most efficient way to convert this int to a timestamp (yyyy-mm-dd hh:mm:ss) using SQL.

2
sqlpostgresqlhiveamazon-redshift

2 Answers

2
votes

Use to_timestamp().

select to_timestamp(20180215::text, 'YYYYMMDD')::timestamp

    to_timestamp     
---------------------
 2018-02-15 00:00:00
(1 row)

The function returns timestamp with time zone. The result depends on the current time zone setting. You can cast the result to timestamp to skip the time zone part.

0
votes

I think this is the best

import datetime
date_format = "%Y%m%d"
timestamp = datetime.datetime.strptime("20180215", date_format).timestamp()
timestamp
# 1518620400.0

you make this better by making function

def date_2_timestamp(date):
    from datetime.datetime import strptime
    return strptime(date, "%Y%m%d").timestamp()