Redshift-将纪元字符串转换为时间戳 [英] Redshift - Convert epoch string to timestamp

问题描述

我有一个包含2列epoch_start和epoch_end的表. 我想找到这两个时期的天数差异.
我面临的问题是上面的列是character changes(5000)类型. 我正在运行的查询是

I have got a table with 2 columns epoch_start and epoch_end. I want to find the difference in days of these 2 epochs.
The problem i am facing is that the above columns are character varying(5000) type. The query im running is

选择datediff(day，'1459762341'，'1450762341')作为天数；

select datediff(day,'1459762341','1450762341') as numdays;

我得到的错误是

错误:时间戳类型为"1459762341"的输入语法无效

ERROR: invalid input syntax for type timestamp: "1459762341"

推荐答案

我找到了解决方案-

1. 要从纪元获取时间戳-

选择(TIMESTAMP'epoch'+'1459762341'* INTERVAL'1 Second')作为 mytimestamp

SELECT (TIMESTAMP 'epoch' + '1459762341' * INTERVAL '1 Second ') as mytimestamp

1. 对于两个纪元之间的datediff-

选择datediff(day，(TIMESTAMP'epoch'+'1458762341'* INTERVAL'1 Second')，(TIMESTAMP'epoch'+'1459762341'* INTERVAL'1 Second'))作为天数;

select datediff(day,(TIMESTAMP 'epoch' + '1458762341' * INTERVAL '1 Second '), (TIMESTAMP 'epoch' + '1459762341' * INTERVAL '1 Second ')) as numdays;

这篇关于Redshift-将纪元字符串转换为时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！