Convert Unix Date to DB2 date

db2inst1.BB_COMPUTERSYSTEM40_V.LASTMODIFIEDTIME_C  this is big int

you can only add 0 to 59 seconds
what is the value in the column?

Hi Senz,

This works just fine:

  select date ('2010-01-05') + cast (second (current_time) as bigint) days from sysibm.sysdummy1


You need to make sure that the number of seconds is in range.



Good Luck,
Kent

Values in column  are below.
 

1276355473819
1276960357473
1276931892210

Why would you want to add 1 TRILLION seconds?

i dont want to add ... but i want to convert unix date to normal date in DB2

what do you mean by unix date?

where do you get it from?

you can always try to convert your seconds to intervals of years , months, days etc...
and then do

select some_date + x years + y monts + z days + a hours + b seconds + c minutes

actually the data in db2 database is in the format
1276355473819
1276960357473
1276931892210
so need to get the proper format

Hi Senz,


DB2 gets it's date from the host operating system and converts it to its own internal format.  The DB2 format is (generally) (10000 * years) + 100 * months + days so that the integer value of today would be 20100622.  

Unix typically uses a running second clock.

Where do you get that unix date?
Why do you need to get at an internals level?


Kent

Hi KDO
i need the dates in user readable format. we cannot recognize what the date time is from
1276355473819
1276960357473
1276931892210  

ho do you obtain these values from the unix system? which command do you run?

Hi Senz,

If those are unix dates (timestamps) you can convert them by supplying your own math.

Taking the first value, 1276355473819.

Unix also keeps a millisecond portion of the clock in that value.  So....

1276355473819 / 1000 = 127635547     Seconds since 1970/01/01 00:00:00 (The epoch time.)
127635547 / 86400 = 1477                     Days since 1970/01/01
127635547 - 127612800 = 22747           Seconds since midnight of current day
22747 / 3600 = 6                                     Current Hour
22747 - 21600 = 1147                             Seconds into the Current Hour
1147 / 60 = 19                                         Minutes into the Current Hour
1147 - 1140 = 7                                       Seconds into the Current Minute.

The result is 1477 days after 1970/01/01 at 06:19:07.

The year calculation is similar, but leap year does complicate it a bit.


Do you really want to go through all of this?

Kent

Hey Kent
Actually i am working on a report and db2 is the database of that now when i extract the value i get the date time as 1276355473819
now to make it user readablei have to achieve the normal date time from
db2inst1.BB_COMPUTERSYSTEM40_V.LASTMODIFIEDTIME_C
 

Hi Senz,

I'm still confused as to where you get that value.

Still, it would seem that a function to convert the value to a timestamp is the most flexible.  You can always recast the result and only need the one function.

I've thrown together the function below, but it's not tested.  Still, it shows you where this needs to go.


Kent

CREATE FUCTION BigIntToTimeStamp (InValue bigint)
RETURNS timestamp
LANGUAGE SQL
DETERMINISTIC
BEGIN ATOMIC
  DECLARE YY int;
  DECLARE MM int;
  DECLARE DD int;
  DECLARE HH int;
  DECLARE MN int;
  DECLARE SS int;
  DECLARE MS int;
  DECLARE LeapDay int;

-- Get the Milleseconds
  SET Ms = MOD (InValue, 1000);
  SET InValue = InValue / 1000;

-- Get the Seconds
  Set SS = MOD (InValue, 60);
  SET InValue = InValue / 60;

-- Get the Minutes
  SET Mn = MOD (InValue, 60);
  SET InValue = InValue / 60;

-- Years are tricky because of leap year.
-- Generally there is 1 extra day in each 4 year group.  The next time that a four year group
-- occurs without an added day is beyond our lifetime, so we can probably ignore it.

-- Get the Number of 4-year blocks and convert to years
  SET YY = MOD (InValue, 365 * 3 + 366) * 4;
  SET InValue = InValue / (365 * 3 + 366);

--  Check if the remaining days extend beyond leap day.  (Relative 0)
  IF (InValue >= 365 * 2 + 31 + 28)
  THEN
    SET LeapDay = 1;
  ELSE
    SET LeapDay = 0;
  END IF;

  SET YY = YY + MOD (InValue - LeapDay, 365);
  SET InValue = InValue - (MOD (InValue - LeapDay, 365) * 365) - LeapDay;

-- Months are more troublesome, but what the heck

  IF (InValue < 31)
    SET MM = 1;
  ELSEIF (InValue < 31 + 28 + LeapDay)
    SET MM = 2;
    SET DD = InValue - 31;
  ELSEIF (InValue < 31 + 28 + LeapDay + 31)
    SET MM = 3;
    SET DD = InValue - (31 + 28 + LeapDay)
  ELSEIF (
etc...

-- Now convert to a string, recast, and return

  return cast (
    RIGHT (DIGITS (1970 + YY, 4)) || '-' ||
    RIGHT (DIGITS (MM, 2)) || '-' ||
    RIGHT (DIGITS (DD, 2)) || '.' ||
    RIGHT (DIGITS (HH, 2)) || '.' ||
    RIGHT (DIGITS (MN, 2)) || '.' ||
    RIGHT (DIGITS (SS, 2)) || '.' ||
    RIGHT (DIGITS (MS, 4)) || '00' as timestamp)
END

Select allOpen in new window

=(((TRUNC(A1/1000)/60)/60)/24)+DATE(1970,1,1)+(10/24)
this gives me the date in excel... but dont know i can formulate in DB2 select query.
 
Also i have only select privilage on the database. so cant create any thing.