How to convert milliseconds, seconds, minutes to DATE on DB2

converting milliseconds, seconds, minutes, days to date on DB2

Milliseconds to DATE:

Where, 1970-01-01, is the start date to calculate this function. The date 1970-01-01is defined as "THE EDGE".

db2 "SELECT( date('1970-01-01')  + (millisecondsfield / 86400000) days   ) as date"

Example:

Creating the table to pratice our examples:

db2 "create table mili (miliseconds bigint)"
db2 "insert into mili (miliseconds) values (1233291600000)"

Performing the select

db2 "SELECT( date('1970-01-01')  + (milliseconds / 86400000) days   ) as date"

Output:

DATE      
----------
01/30/2009

  1 record(s) selected.

Seconds to DATE:

Where, 1970-01-01, is the start date to calculate this function. The date 1970-01-01is defined as "THE EDGE".

db2 "SELECT( date('1970-01-01')  + (seconds / 86400) days   ) as date"

Example:

Creating the table to pratice our examples:

db2 "create table mili (seconds bigint)"
db2 "insert into mili (seconds) values (1233291600)"

Performing the select

db2 "SELECT( date('1970-01-01')  + (seconds / 86400) days   ) as date"

Output:

DATE      
----------
01/30/2009

  1 record(s) selected.

Minutes to DATE:

Where, 1970-01-01, is the start date to calculate this function. The date 1970-01-01is defined as "THE EDGE".

db2 "SELECT( date('1970-01-01')  + (minites / 1140) days   ) as date"

Example:

Creating the table to pratice our examples:

db2 "create table mili (minutes bigint)"
db2 "insert into mili (minutes) values (20554860)"

Performing the select

db2 "SELECT( date('1970-01-01')  + (minitues / 1440) days   ) as date"

Output:

DATE      
----------
01/30/2009

  1 record(s) selected.

Hours to DATE:

Where, 1970-01-01, is the start date to calculate this function. The date 1970-01-01is defined as "THE EDGE".

db2 "SELECT( date('1970-01-01')  + (hours / 24) days   ) as date"

Example:

Creating the table to practice our examples:

db2 "create table mili (hours bigint)"
db2 "insert into mili (hours) values (342581)"

Performing the select

db2 "SELECT( date('1970-01-01')  + (hours / 24) days   ) as date"

Output:

DATE      
----------
01/30/2009

  1 record(s) selected.