Unix Time to Date conversion in SQL for DB2 DB2/400 or iSeries or IBM i

For those of you who are in search of converting a UNIX Epoch Timestamp or whatever this is called to a readable date in DB2/400 SQL may have found this posting here [1]. However, this does not translate to the beloved IBM i SQL syntax so I converted it to something that will run on the IBM i.

The Function

CREATE FUNCTION e2d (ept INT)                      
RETURNS CHAR(23)                                    
RETURN                                              
(CHAR(DATE(INT(ept/86400) + DAYS('1970-01-01')), ISO))
|| ' ' ||                                           
repeat('0', 2 - length(rtrim(mod(int(ept/3600), 24))))
            || rtrim(mod(int(ept/3600), 24))        
|| ':' ||                                           
repeat('0', 2 - length(rtrim(mod(int(ept/60), 60))))
            || rtrim(mod(int(ept/60), 60))          
|| ':' ||                                           
repeat('0', 2 - length(rtrim(mod(int(ept), 60))))   
            || rtrim(mod(int(ept), 60))             
|| ' UTC'                                            

How to use

The timestamps can be included with milliseconds which this function does not accept so you either change the function (or let me do it for $100) or strip the milliseconds from the value.

Value in File

> select expected_start_date from item

Result:

      EXPECTED_START_DATE   
        1.368.965.867.668   
        1.369.138.667.695   
        1.369.138.667.658   
        1.423.064.635.396   
        1.356.951.946.378   
        1.369.052.267.674   
        1.358.246.021.420   
        1.370.953.067.703   
        1.369.052.267.689   
        1.369.052.267.671   
        1.413.212.245.996   
        1.366.979.412.834   

Value in File with Conversion

> select e2d(int(expected_start_date/1000)) as expected_start_date from item

Result:

EXPECTED_START_DATE   
2013-05-19 12:17:47 UTC
2013-05-21 12:17:47 UTC
2013-05-21 12:17:47 UTC
2015-02-04 15:43:55 UTC
2012-12-31 11:05:46 UTC
2013-05-20 12:17:47 UTC
2013-01-15 10:33:41 UTC
2013-06-11 12:17:47 UTC
2013-05-20 12:17:47 UTC
2013-05-20 12:17:47 UTC
2014-10-13 14:57:25 UTC
2013-04-26 12:30:12 UTC
2013-02-04 16:15:57 UTC
2013-02-03 23:27:41 UTC

Cheers,

Wim
wim.jongman at remainsoftware com

[1] http://www.walkernews.net/2014/02/08/db2-sql-function-that-converts-unix-epoch-time-to-calendar-date/