teradata - 如何在 Teradata 中将纪元时间转换为人类可读

Question

在我的 Teradata 表中，dhTimestamp 列下有纪元时间戳

dhTimestamp  
1435308067705  
1434965874565  
1434763800794  
1434775876034  
1434765207057  

如何在 Teradata 上将纪元时间戳转换为人类日期/时间格式？

Answer 1

这是标准 unixtime 的 SQL UDF:

/**********
Converting Unix/POSIX time to a Timestamp 

Unix time: Number of seconds since 1970-01-01 00:00:00 UTC not counting leap seconds (currently 24 in 2011)

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)

Can be changed to use BIGINT instead of INTEGER

20101211 initial version - Dieter Noeth
**********/

REPLACE FUNCTION Epoch2Timestamp (UnixTime INT)
RETURNS TimeStamp(0)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
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)
;

SELECT
   Epoch2Timestamp(-2147483648)
  ,Epoch2Timestamp(0)
  ,Epoch2Timestamp(2147483647)
;

但是您的值似乎包括毫秒，这需要修改计算:

CAST(DATE '1970-01-01' + (UnixTime / 86400000) AS TIMESTAMP(3))
+ ((UnixTime / 1000.000 MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND)

编辑 2016-07-01:

夏令时存在问题(参见 DevEx 上 Teradata 的 this thread)，这应该可以解决:

-- Unix time to Timestamp WITH TIME ZONE (+00:00)
REPLACE FUNCTION UnixTime_to_TimeStamp_TZ (UnixTime INT)
RETURNS TIMESTAMP(0) WITH TIME ZONE
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
  ((CAST(DATE '1970-01-01' + (UnixTime / 86400) AS TIMESTAMP(0) AT 0)) AT 0)
+ ((UnixTime MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND);

-- Unixtime to Timestamp, implicit TIME ZONE of the local session
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(((CAST(DATE '1970-01-01' + (UnixTime / 86400) AS TIMESTAMP(0) AT 0)) AT 0)
     + ((UnixTime MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND) AS TIMESTAMP(0));