I am not yet savey with the data functions in Teradata, and I have the following issue:
I am consuming telcom information from Cisco and they store their transaction times as the number of seconds since 1/1/1970. So I have values that look like this:
'1484002904'. I have tried using the interval combinations with dividing down to months or even years but I lose the precision or it creates and overflow for the interval type. What I need to do is quite simple in theory. 1/1/1970 00:00:00 + 1484002904 seconds.
I know it will probably take a combination of functions/calculations, but how to I hold the precision and hit what I want
I would strongly recommend a C UDF for this - it would be the easiest to understand and maintain. For a start, given "seconds":
days = seconds/86400 + 1
yrs4 = 365*4+1 /* days in 4 years
yr = 1970 + 4*days/yrs4 + (days % yrs4)/365
d = (days % yrs4) % 365
That gives you the year and the day (d) in the year (Julian day). Then you could create two arrays of month-days, one for leap years and one for other years, and use those to calculate the month and day. This could be done in an SQL function, but converting the Julian day to month-day would be a couple of long nested case operations; expressing this in a programming language would look like:
mo = d < 32 ? 1 : d < 60 ? 2 : d < 91 ? 3 : d < 121 ? 4 : d < 152 ? 5 : d < 182 ? 6 : d < 213 ? 7 : d < 244 ? 8 : d < 274 ? 9 : d < 305 ? 10 : d < 335 ? 11 : 12;
and so on... You can imagine how long this would be with CASE expressions.
The time of day can be calculated from seconds % 86400 (seconds mod 86400 in SQL) - that gives you the number of seconds in the day. Divide by 3600 to get the hour; divide seconds-in-the-day % 3600 by 60 to get the minute, and seconds-in-the-day % 60 is the second.
These are two SQL-UDFs to convert Unixtime to Timestamp & back:
/********** 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 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) ; SELECT
UnixTime_to_TimeStamp(-2147483648) ,UnixTime_to_TimeStamp(0) ,UnixTime_to_TimeStamp(2147483647) ; /********** Converting a Timestamp to Unix/POSIX time Unix time: Number of seconds since 1970-01-01 00:00:00 UTC not counting leap seconds (currently 24 in 2011) 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 TimeStamp_to_UnixTime (ts TimeStamp(6)) RETURNS INTEGER 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)) ; SELECT TimeStamp_to_UnixTime(TIMESTAMP '1901-12-13 20:45:52') ,TimeStamp_to_UnixTime(CURRENT_TIMESTAMP) ,TimeStamp_to_UnixTime(TIMESTAMP '2038-01-19 03:14:07') ;
You can also use TO_TIMESTAMP, but this always returns a TIMESTAMP(6) :
SELECT To_Timestamp(1484002904), UnixTime_to_TimeStamp(1484002904)