teradata 中时间戳到纪元的转换 [英] Timestamp to epoch conversion in teradata
本文介绍了teradata 中时间戳到纪元的转换的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 teradata 表中有一个 TIMESTAMP 列.我想将时间戳转换为纪元值.有人可以解释一下如何做到这一点.
I have a TIMESTAMP column in a teradata table. I want to convert the timestamp to epoch value. Can someone shed some light on how to do this.
推荐答案
这是我几年前写的 SQL UDF.
This is a SQL UDF I wrote a few years ago.
如果您没有创建函数的访问权限,您可能会要求您轻拍或简单地剪切 &粘贴计算.
If you don't have access rights to create a function, you mighty ask you dab or simply cut & paste the calculation.
/**********
Converting a Timestamp to Unix/POSIX/epoch time
Unix time: Number of seconds since 1970-01-01 00:00:00 UTC not counting leap seconds (currently 34 in 2010)
The maximum range of Timestamps is based on the range of INTEGERs:
1901-12-13 20:45:52 (-2147483648) to 2038-01-19 03:14:07 (2147483647)
Simply change to BIGINT to cover the full Teradata date range
20101211 initial version - Dieter Noeth
**********/
REPLACE FUNCTION TimeStamp_to_UnixTime (ts TIMESTAMP(6))
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST(ts AS DATE) - DATE '1970-01-01') * 86400
+ (EXTRACT(HOUR FROM ts) * 3600)
+ (EXTRACT(MINUTE FROM ts) * 60)
+ (EXTRACT(SECOND FROM ts))
;
反转计算:
/**********
Converting Unix/POSIX/epoch time to a Timestamp
Unix time: Number of seconds since 1970-01-01 00:00:00 UTC not counting leap seconds (currently 34 in 2010)
Also working for negative numbers.
The maximum range of Timestamps is based on the range of INTEGERs:
1901-12-13 20:45:52 (-2147483648) to 2038-01-19 03:14:07 (2147483647)
Simply change to BIGINT to cover the full Teradata date range
20101211 initial version - Dieter Noeth
**********/
REPLACE FUNCTION UnixTime_to_TimeStamp (UnixTime INT)
RETURNS TimeStamp(0)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
CAST(DATE '1970-01-01' + (UnixTime / 86400) AS TIMESTAMP(0))
+ ((UnixTime MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND)
;
在 TD14 中使用 TO_TIMESTAMP(UnixTime) 更容易,但这仅限于 INTEGER 范围.
It's is easier in TD14 using TO_TIMESTAMP(UnixTime), but this is restricted to the INTEGER range.
这篇关于teradata 中时间戳到纪元的转换的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文