DB2 Epoch Conversion

As we have progresses with our move from PostgreSQL to DB2, we discovered that DB2 doesn’t have internal Epoch functions to deal with the epoch time format.  Luckily, we are running DB2 on AIX, and most Unix tools are readily available.  We tried many different variations, including writing internal Epoch conversion in SQL.  Most appeared too slow, so we ended up preprocessing the data externally.  The most useful page I found to work with Epoch was Epoch Time Converter.   There are perl specific examples here: Perl Epoch Routines

Here is how to create a DB2 compatible timestamp in Perl:

use DateTime;
sub epoch_to_db2 {
($tsp) = @_; # receive one parameter: the epoch time
$dt = DateTime->from_epoch( epoch => $tsp );
$ymd = $dt->ymd; # 1974-11-30
$hms = $dt->hms('.'); # 13.30.00
$tsp = "$ymd-$hms"; # DB2 format YYYY-MM-DD-hh.mm.ss
return $tsp;
}

Here are the key paragraphs from the referenced pages:

• What is epoch time?
  The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds.
  The epoch timestamp 0 can be written in ISO 8601 format as: 1970-01-01T00:00:00Z. One epoch hour is 3600 seconds, one epoch day is 86400 seconds long, leap seconds are not calculated.
  Many Unix systems store epoch dates as a signed 32-bit integer, which might cause problems on January 19, 2038 (known as the Year 2038 problem or Y2038).

  Human readable time	Seconds
  1 minute	60 seconds
  1 hour	3600 seconds
  1 day	86400 seconds
  1 week	604800 seconds
  1 month (30.44 days)	2629743 seconds
  1 year (365.24 days)	31556926 seconds

• How to get the current epoch time in …
  

  Perl	time
  PHP	time()
  Ruby	Time.now (or Time.new). To display the epoch: Time.now.to_i
  Python	import time first, then time.time()
  Java	long epoch = System.currentTimeMillis()/1000;
  Microsoft .NET C#	epoch = (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
  VBScript/ASP	DateDiff("s", "01/01/1970 00:00:00", Now())
  MySQL	SELECT unix_timestamp(now()) More information
  PostgreSQL	SELECT extract(epoch FROM now());
  SQL Server	SELECT DATEDIFF(s, '19700101', GETDATE())
  JavaScript	Math.round(new Date().getTime()/1000.0) getTime() returns time in milliseconds.
  Unix/Linux	date +%s
  Other OS’s	Command line: perl -e "print time" (If Perl is installed on your system)

• Convert from human readable date to epoch
  

  Perl	Use these Perl Epoch routines
  PHP	mktime(hour, minute, second, month, day, year) More information
  Ruby	Time.local(year, month, day, hour, minute, second, usec ) (or Time.gm for GMT/UTC input). To display add .to_i
  Python	import time first, then int(time.mktime(time.strptime('2000-01-01 12:34:00', '%Y-%m-%d %H:%M:%S')))
  Java	long epoch = new java.text.SimpleDateFormat ("dd/MM/yyyy HH:mm:ss").parse("01/01/1970 01:00:00");
  VBScript/ASP	DateDiff("s", "01/01/1970 00:00:00", time field) More information
  MySQL	SELECT unix_timestamp(time) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD More on using Epoch timestamps with MySQL
  PostgreSQL	SELECT extract(epoch FROM date('2000-01-01 12:34')); With timestamp: SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08'); With interval: SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
  SQL Server	SELECT DATEDIFF(s, '19700101', time field)
  JavaScript	use the JavaScript Date object
  Unix/Linux	date +%s -d"Jan 1, 1980 00:00:01"

• Convert from epoch to human readable date
  

  Perl	Use these Perl Epoch routines
  PHP	date(output format, epoch); Output format example: ‘r’ = RFC 2822 date More information
  Ruby	Time.at(epoch)
  Python	import time first, then time.gmtime(epoch) time is an array of year, month, day, hour, min, sec, day of week, day of year, DST More information
  Java	String date = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000));
  VBScript/ASP	DateAdd("s", epoch, "01/01/1970 00:00:00") More information
  PostgreSQL	SELECT TIMESTAMP WITH TIME ZONE 'epoch' + epoch * INTERVAL '1 second';
  MySQL	from_unixtime(epoch, optional output format) The default output format is YYY-MM-DD HH:MM:SS more …
  SQL Server	DATEADD(s, epoch, '19700101')
  Microsoft Excel	=(A1 / 86400) + 25569 Format the result cell for date/time, the result will be in GMT time (A1 is the cell with the epoch number). For other timezones: =((A1 +/- timezone adjustment) / 86400) + 25569.
  JavaScript	use the JavaScript Date object
  Linux	date -d @1190000000 (replace 1190000000 with your epoch, needs newer version of date)
  Other OS’s	Command line: perl -e "print scalar(localtime(epoch))" (If Perl is installed) Replace ‘localtime’ with ‘gmtime’ for GMT/UTC time.

• Converting from epoch to normal date in Perl
  Using the internal localtime or gmtime functions,
  localtime and gmtime return an array:

  my $time = time;	# or any other epoch timestamp
  my @months = ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
  my ($sec, $min, $hour, $day,$month,$year) = (localtime($time))[0,1,2,3,4,5,6];
  # You can use 'gmtime' for GMT/UTC dates instead of 'localtime'
  print "Unix time ".$time." converts to ".$months[$month]." ".$day.", ".($year+1900);
  print " ".$hour.":".$min.":".$sec."n";

  But you can also use the scalar function to display your date with far less code:
  print scalar localtime(946684800);
  returns Sat Jan 1 01:00:00 2000 (in my timezone).

  For more advanced date manipulation, try using the DataTime module:

  use DateTime;
  $dt = DateTime->from_epoch( epoch => $epoch );
  $year   = $dt->year;
  $month  = $dt->month; # 1-12 - also mon
  $day    = $dt->day; # 1-31 - also day_of_month, mday
  $dow    = $dt->day_of_week; # 1-7 (Monday is 1) - also dow, wday
  $hour   = $dt->hour; # 0-23
  $minute = $dt->minute; # 0-59 - also min
  $second = $dt->second; # 0-61 (leap seconds!) - also sec
  $doy    = $dt->day_of_year; # 1-366 (leap years) - also doy
  $doq    = $dt->day_of_quarter; # 1.. - also doq
  $qtr    = $dt->quarter; # 1-4
  $ymd    = $dt->ymd; # 1974-11-30
  $ymd    = $dt->ymd('/'); # 1974/11/30 - also date
  $hms    = $dt->hms; # 13:30:00
  $hms    = $dt->hms('|'); # 13!30!00 - also time