如何在红移中节省时间

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

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

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