| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-19-08, 14:09
|
|
Registered User
|
|
Join Date: Dec 2002
Posts: 123
|
|
|
Date function to return UNIX timestamp?
|
|
Hi,
Is there any DB2 date function that returns a timestamp that is a long integer containing the number of seconds between January 1 1970 00:00:00 GMT and a date specified? Thanks!
|
|
12-19-08, 14:22
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
|
|
12-19-08, 16:49
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
|
|
Timestampdiff is an approximation, which uses 30 days' long months.
This approach works way better:
Code:
values(
(days(current_timestamp-current_timezone) - days('1970-01-01') )*86400 +
midnight_seconds(current_timestamp - current_timezone)
)
|
|
12-21-08, 08:06
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
@n_i: your approach is also only an approximation because you ignore leap years as well as leap seconds.
@db2user: what is the underlying requirement to have seconds since 1970-01-01? Could you elaborate on that? Maybe there is a completely different solution that is different from your approach.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
12-21-08, 10:32
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Quote:
|
Originally Posted by stolze
@n_i: your approach is also only an approximation because you ignore leap years as well as leap seconds.
|
Even if it were true, it would not be me but rather the DAYS function 
However, the formula produces the same result as the date utilitiy on AIX, so I'm thinking it does account for leap years and seconds as it should.
|
|
12-21-08, 12:18
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
You are right on the leap years - my fault. But you do not account for leap seconds because you assume 86400 seconds for every day. There are a few days which actually have 86401 seconds, however.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
12-21-08, 14:49
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,196
|
|
Quote:
|
Originally Posted by stolze
You are right on the leap years - my fault. But you do not account for leap seconds because you assume 86400 seconds for every day. There are a few days which actually have 86401 seconds, however.
|
Knut, when is the last time you took a day of vacation?
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
|
|
12-21-08, 15:25
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Quote:
|
Originally Posted by stolze
Y But you do not account for leap seconds because you assume 86400 seconds for every day. There are a few days which actually have 86401 seconds, however.
|
I think that is only partially true, because the leap seconds don't accumulate over time. When the leap second is added to adjust for the slowing Earth rotation, the time is paused for 1 second, so there's a "hole in time" at midnight. In other words, as an example, there are 86400 seconds on December 31 and 86400 seconds on January 1st, with 1-second pause in between. Therefore, the formula will return a possibly incorrect result only during that leap 1 second long pause, which I think is an acceptable precision.
|
|
12-21-08, 15:56
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
Quote:
|
Originally Posted by stolze
what is the underlying requirement to have seconds since 1970-01-01?
|
That's the standard way a timestamp is stored on a UNIX (or Linux) system.
E.g., the last modification time of a file is stored as a 4-byte integer (with the above meaning) as an attribute of the inode.
So a useful reason for obtaining such a value within DB2 is to match timestamps of DB2 with timestamps of the operating system.
Be careful with daylight-saving-time jumps, though! UNIX really does what it says (# seconds since 1970-01-01-00:00:00 UTC), while DB2 uses local time, i.e., will typically report 3601 seconds time difference while in reality only one second elapsed, if you do the timediff around the spring DST switch moment.
This justifies the current_timezone element in Nick's answer, which effectively converts the current_timestamp to UTC.
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
|
|
12-22-08, 03:23
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
That is well understood. My question was what this timestamp will be compared against, i.e. what is the application doing with it. Maybe it is trivial to change working ISO timestamps, i.e. yyyy-mm-dd-HH.MM.SS?
Regarding leap seconds in UNIX/Linux systems, you will find such a definition of "struct tm" in /usr/include/time.h:
Code:
struct tm
{
int tm_sec; /* Seconds. [0-60] (1 leap second) */
int tm_min; /* Minutes. [0-59] */
int tm_hour; /* Hours. [0-23] */
int tm_mday; /* Day. [1-31] */
int tm_mon; /* Month. [0-11] */
int tm_year; /* Year - 1900. */
int tm_wday; /* Day of week. [0-6] */
int tm_yday; /* Days in year.[0-365] */
int tm_isdst; /* DST. [-1/0/1]*/
Whether the OP has to care about it and needs the accuracy here or can be a bit sloppy, I do not know...
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
Last edited by stolze; 12-22-08 at 03:27.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Date function to return UNIX timestamp?
