Converting Teradata Timestamp to Epoch

Question

A version of this question has been asked and answered a couple of years ago: Timestamp to epoch conversion in teradata.

However, there is a comment in one of the answers that leads me to believe that there is possibly a new way to solve this problem. Since my "reputation" is not high enough, I cannot ask a question of the person making the comment which is why I'm asking the question again.

I'm trying to convert CURRENT_TIMESTAMP to Epoch time in Teradata. I would like to stay away from a UDF. The comment in the previous question mentioned a Teradata function for converting Epoch time to TIMESTAMP. So, my real question is "Is the a new'ish Teradata function for easily converting TIMESTAMP to Epoch?"

Answer 1

I'm working on TD 15.10.
Please note that large integers literals might be interpreted by default as decimals with 0 precision, therefore require casting in order to be accepted by the function.

select  to_timestamp(cast(-62135596800 as bigint))          as min_legal_val
       ,to_timestamp(0)                                     as zero
       ,to_timestamp(cast (9223372036854775807 as bigint))  as max_val
;

---

+----------------------------+----------------------------+----------------------------+
| min_legal_val              | zero                       | max_val                    |
+----------------------------+----------------------------+----------------------------+
| 0001-01-01 00:00:00.000000 | 1970-01-01 00:00:00.000000 | 2548-01-00 15:30:07.000000 |
+----------------------------+----------------------------+----------------------------+

Answer 2

Sure you can.. It's # of seconds from 1970-01-01 00:00:00. See sample below

select 
 CAST(1680733439 AS BIGINT) EPOCH_TIME 
,to_timestamp(EPOCH_TIME) UTC
,to_timestamp(EPOCH_TIME) at 'GMT' as GMT
,cast(cast(UTC as char(19))||'+00:00' as timestamp(0) with time zone) AT 'America Central'  as DTTM
/* Break down the timestamp, if needed */
,cast( DTTM as date ) as DT
,extract( hour from DTTM ) as HR
,extract( minute from DTTM ) as MI
,extract( second from DTTM ) as SC
,extract( timezone_hour from DTTM ) as tzhr /* if needed.. */
/* EPOCH from timestamp. */
, (cast( DTTM as date ) - date '1970-01-01') * 86400 +
  (extract( hour from DTTM ) * 3600) + 
  (extract( minute from DTTM ) * 60)  +
  (cast(extract( second from DTTM ) as integer ) ) -
  (extract( timezone_hour from DTTM ) * 3600) 
  EPOCH_CALC
, case when EPOCH_TIME = EPOCH_CALC then 'Worked' else 'Did not work' end as ok