How to convert epoch time to Human readable in Teradata -

-

in teradata table, have epoch timestamps under column dhtimestamp

dhtimestamp   1435308067705   1434965874565   1434763800794   1434775876034   1434765207057

how can convert epoch timestamp human date/time format on teradata?

this sql udf standard unixtime:

/********** converting unix/posix time timestamp   unix time: number of seconds since 1970-01-01 00:00:00 utc not counting leap seconds (currently 24 in 2011)  working negative numbers. maximum range of timestamps based on range of integers: 1901-12-13 20:45:52 (-2147483648) 2038-01-19 03:14:07 (2147483647)  can changed 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) timestamp(0)) + ((unixtime mod 86400) * interval '00:00:01' hour second) ;  select    epoch2timestamp(-2147483648)   ,epoch2timestamp(0)   ,epoch2timestamp(2147483647) ;

but values seem include milliseconds, needs modified calculation:

cast(date '1970-01-01' + (unixtime / 86400000) timestamp(3)) + ((unixtime / 1000.000 mod 86400) * interval '00:00:01' hour second)

edit 2016-07-01:

there issue dayight saving time (see this thread on teradata's on devex), should fix it:

-- unix time timestamp time zone (+00:00) replace function unixtime_to_timestamp_tz (unixtime int) returns timestamp(0) time zone language sql contains sql deterministic sql security definer collation invoker inline type 1 return   ((cast(date '1970-01-01' + (unixtime / 86400) timestamp(0) @ 0)) @ 0) + ((unixtime mod 86400) * interval '00:00:01' hour second);  -- unixtime timestamp, implicit time zone of 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) timestamp(0) @ 0)) @ 0)      + ((unixtime mod 86400) * interval '00:00:01' hour second) timestamp(0));

Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more