Importing table from MySql to RedShift, MySql have to columns which is time
datatype
从MySql导入表到RedShift, MySql必须有时间数据类型的列
+-----------------+-------------+------+-----+---------+----------------+
| 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
由于导入表slot_start_time之后,Redshift没有时间数据类型,所以slot_end_time列在这种情况下返回1970-01-01,slot_end_time显示为日期数据类型
When I ALTER
both column in Redshift as timestamp then it will return '01/01/70 HH:MM'
当我将两列以红移格式更改为时间戳时,它将返回'01/01/70 HH:MM'
So how I can save only time in RedShift.
所以我只能在红移中节省时间。
2 个解决方案
#1
4
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.
我强烈建议不要尝试这种冒险。您最终可能会使用一些不兼容的数据类型,比如string,它实际上可能帮助您存储时间,但是当您开始查询数据时,它将是灾难性的。
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.
在最坏的情况下,如果Redshift发现数据类型不兼容,并且整个列将填充空值,那么它甚至可能不会填充date列。
In the Redshift table, store the entire timestamp in the format
在Redshift表中,以这种格式存储整个时间戳
yyyy-mm-dd hh:mm:ss (use any placeholder date in the format; as date is irrelevant to you in any case)
yyyyyy -mm-dd hh:mm:ss(使用格式中的任何占位符日期;在任何情况下,日期都与你无关
and use any of the functions to extract only the timestamp while querying the table (an illustration is shown below).
并且使用任何函数在查询表时只提取时间戳(下图)。
#2
0
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.
由于在Redshift中没有时间列,所以可以使用相同的日期,例如'1970-01-01',然后添加时间。
then you will be able to run date_part on the field.
然后您将能够在字段上运行date_part。
for example
例如
select extract(hour from timestamp '2010-02-16 20:38:40');
date_part
#1
4
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.
我强烈建议不要尝试这种冒险。您最终可能会使用一些不兼容的数据类型,比如string,它实际上可能帮助您存储时间,但是当您开始查询数据时,它将是灾难性的。
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.
在最坏的情况下,如果Redshift发现数据类型不兼容,并且整个列将填充空值,那么它甚至可能不会填充date列。
In the Redshift table, store the entire timestamp in the format
在Redshift表中,以这种格式存储整个时间戳
yyyy-mm-dd hh:mm:ss (use any placeholder date in the format; as date is irrelevant to you in any case)
yyyyyy -mm-dd hh:mm:ss(使用格式中的任何占位符日期;在任何情况下,日期都与你无关
and use any of the functions to extract only the timestamp while querying the table (an illustration is shown below).
并且使用任何函数在查询表时只提取时间戳(下图)。
#2
0
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.
由于在Redshift中没有时间列,所以可以使用相同的日期,例如'1970-01-01',然后添加时间。
then you will be able to run date_part on the field.
然后您将能够在字段上运行date_part。
for example
例如
select extract(hour from timestamp '2010-02-16 20:38:40');
date_part