CODEWITHSUNDEEP
mysqldatetimetimeamazon-redshift
Ashutosh SIngh
Ashutosh SIngh

Reputation: 1019

How save only time in Redshift HH:MM:SS

Importing table from MySql to RedShift, MySql have to columns which is time datatype

+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| id              | int(11)     | NO   | PRI | NULL    | auto_increment |
| typecode        | varchar(20) | NO   |     | NULL    |                |
| slot_start_time | time        | NO   |     | NULL    |                |
| slot_end_time   | time        | NO   |     | NULL    |                |
| parent_id       | int(11)     | NO   |     | NULL    |                |
| createdon       | datetime    | NO   |     | NULL    |                |
| modifiedon      | datetime    | NO   |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+

Redshift doesn't have time data type because of this after importing table slot_start_time, slot_end_time columns return 1970-01-01 in this case slot_start_time, slot_end_time show as Date datatype

When I ALTER both column in Redshift as timestamp then it will return '01/01/70 HH:MM'

So how I can save only time in RedShift.

Upvotes: 1

Views: 6244

Answers (2)

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

I would strongly suggest against trying out such venture. You might end up using some non-compatible datatype, like string which might actually help you out just to store the time but would be catastrophic when you actually begin to query your data.

In worst case, Redshift might not even populate the date column if it finds the datatype not compatible and the entire column will be populated with null values.

In the Redshift table, store the entire timestamp in the format

yyyy-mm-dd hh:mm:ss (use any placeholder date in the format; as date is irrelevant to you in any case)

and use any of the functions to extract only the timestamp while querying the table (an illustration is shown below).

enter image description here

Upvotes: 4

user3600910
user3600910

Reputation: 3109

since you don't have time column in Redshift ,you can use the same date , for example '1970-01-01' and then to add the time.

then you will be able to run date_part on the field.

for example

select extract(hour from timestamp '2010-02-16 20:38:40');
date_part

Upvotes: 0

Related Questions