CREATE FUNCTION UNIX_TIMESTAMP (P1 TIMESTAMP)

RETURNS INTEGER

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

NO EXTERNAL ACTION

RETURN

VALUES (DAYS(P1) - DAYS('1970-01-01')) * 24 * 60 *
60+hour(P1)*3600+minute(P1)*60+second(P1)

;

Zsolt Koppany <zkoppanylist@intland.com> wrote:

> CREATE FUNCTION UNIX_TIMESTAMP (P1 TIMESTAMP)
> RETURNS INTEGER
> LANGUAGE SQL
> NOT DETERMINISTIC
> CONTAINS SQL
> NO EXTERNAL ACTION
> RETURN
> VALUES (DAYS(P1) - DAYS('1970-01-01')) * 24 * 60 *
> 60+hour(P1)*3600+minute(P1)*60+second(P1)
>
> ;

You are ignoring the leap seconds, but otherwise it looks fine to me. Any
specific reason why you ask?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Hi Knut,

what are "leap seconds"? I would like to get a correct information, thus I
don't want to ignore "leap seconds". Can you help me how to fix my function?

Zsolt

"Knut Stolze" <stolze@de.ibm.com> schrieb im Newsbeitrag
news:bmdu9g$21o$1@fsuj29.rz.uni-jena.de...
> Zsolt Koppany <zkoppanylist@intland.com> wrote:
>
> > CREATE FUNCTION UNIX_TIMESTAMP (P1 TIMESTAMP)
> > RETURNS INTEGER
> > LANGUAGE SQL
> > NOT DETERMINISTIC
> > CONTAINS SQL
> > NO EXTERNAL ACTION
> > RETURN
> > VALUES (DAYS(P1) - DAYS('1970-01-01')) * 24 * 60 *
> > 60+hour(P1)*3600+minute(P1)*60+second(P1)
> >
> > ;
>
> You are ignoring the leap seconds, but otherwise it looks fine to me. Any
> specific reason why you ask?
>
> --
> Knut Stolze
> Information Integration
> IBM Germany / University of Jena

Zsolt Koppany <zkoppanylist@intland.com> wrote:

> Hi Knut,
>
> what are "leap seconds"? I would like to get a correct information, thus I
> don't want to ignore "leap seconds". Can you help me how to fix my
> function?

Lepa seconds are used to compensate for the difference of Earth's rotation
(which is not constant!) and the time computed by atomic clocks. If the
earth rotation is slower, the atomic clocks would be too fast, so leap
seconds are sometimes introduced to make sure that noon will still be at
12pm in the future.

Here is some additional reading material (just got it from google):
http://tycho.usno.navy.mil/leapsec.html
http://www.nmm.ac.uk/site/request/se.../contentId/347
http://www.boulder.nist.gov/timefreq...leapsecond.htm

This might answer your question whether you want to take leap seconds into
account or not. If you do, you might want to change your functions like
this:

CREATE FUNCTION UNIX_TIMESTAMP ( p1 TIMESTAMP )
RETURNS INTEGER
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
RETURN DAYS(p1) - DAYS('1970-01-01')) * 24 * 3600 +
HOUR(p1) * 3600 +
MINUTE(p1) * 60 +
SECOND(p1) +
CASE
WHEN DATE(p1) > DATE('1998-12-31') THEN 22
WHEN DATE(p1) > DATE('1997-06-30') THEN 21
WHEN DATE(p1) > DATE('1995-12-31') THEN 20
...
WHEN DATE(p1) > DATE('1972-06-30') THEN 1
ELSE 0
END

You have to adjust your function whenever a new leap second is introduced.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Knut Stolze wrote:
> Zsolt Koppany <zkoppanylist@intland.com> wrote:
>>what are "leap seconds"?
>
> Lepa seconds are used to compensate for the difference of Earth's rotation
> (which is not constant!) and the time computed by atomic clocks.
>
> This might answer your question whether you want to take leap seconds into
> account or not.

You probably don't want to take them into account - POSIX.1 explicitly
does not, so the operating system doesn't either (in general).

> If you do, you might want to change your functions like
> this: [...]
> You have to adjust your function whenever a new leap second is introduced.

No leap seconds have been introduced for a number of years now. And
the information is posted a month or two in advance when they are
introduced, so it is possible to handle them in a timely manner. Of
course, predicting the future is even harder than usual. Indeed,
there is discussion about dropping them forever.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

MySql has a function UNIX_TIMESTAMP and we are porting our application to
support DB2. Because DB2 doesn't support this function we have to write our
own, but this function should do exactly what MySql does.

Here is the description of this function (from the MySql documentation):

UNIX_TIMESTAMP()'
`UNIX_TIMESTAMP(date)'
If called with no argument, returns a Unix timestamp (seconds since
`'1970-01-01 00:00:00'' GMT) as an unsigned integer. If
`UNIX_TIMESTAMP()' is called with a `date' argument, it returns
the value of the argument as seconds since `'1970-01-01 00:00:00''
GMT. `date' may be a `DATE' string, a `DATETIME' string, a
`TIMESTAMP', or a number in the format `YYMMDD' or `YYYYMMDD' in
local time:

mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580

When `UNIX_TIMESTAMP' is used on a `TIMESTAMP' column, the function
will return the internal timestamp value directly, with no implicit
"string-to-unix-timestamp" conversion. If you pass an
out-of-range date to `UNIX_TIMESTAMP()' it will return 0, but
please note that only basic checking is performed (year 1970-2037,
month 01-12, day 01-31).

If you want to subtract `UNIX_TIMESTAMP()' columns, you may want to
cast the result to signed integers. *Note Cast Functions::.



Zsolt
"Jonathan Leffler" <jleffler@earthlink.net> schrieb im Newsbeitrag
news:3YKib.463$7a4.177@newsread4.news.pas.earthlin k.net...
> Knut Stolze wrote:
> > Zsolt Koppany <zkoppanylist@intland.com> wrote:
> >>what are "leap seconds"?
> >
> > Lepa seconds are used to compensate for the difference of Earth's
rotation
> > (which is not constant!) and the time computed by atomic clocks.
> >
> > This might answer your question whether you want to take leap seconds
into
> > account or not.
>
> You probably don't want to take them into account - POSIX.1 explicitly
> does not, so the operating system doesn't either (in general).
>
> > If you do, you might want to change your functions like
> > this: [...]
> > You have to adjust your function whenever a new leap second is
introduced.
>
> No leap seconds have been introduced for a number of years now. And
> the information is posted a month or two in advance when they are
> introduced, so it is possible to handle them in a timely manner. Of
> course, predicting the future is even harder than usual. Indeed,
> there is discussion about dropping them forever.
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler@earthlink.net, jleffler@us.ibm.com
> Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
>

Zsolt Koppany <zkoppanylist@intland.com> wrote:

> MySql has a function UNIX_TIMESTAMP and we are porting our application to
> support DB2. Because DB2 doesn't support this function we have to write
> our own, but this function should do exactly what MySql does.
>
> Here is the description of this function (from the MySql documentation):
>
> UNIX_TIMESTAMP()'
> `UNIX_TIMESTAMP(date)'
> If called with no argument, returns a Unix timestamp (seconds since
> `'1970-01-01 00:00:00'' GMT) as an unsigned integer. If
> `UNIX_TIMESTAMP()' is called with a `date' argument, it returns
> the value of the argument as seconds since `'1970-01-01 00:00:00''
> GMT. `date' may be a `DATE' string, a `DATETIME' string, a
> `TIMESTAMP', or a number in the format `YYMMDD' or `YYYYMMDD' in
> local time:
>
> mysql> SELECT UNIX_TIMESTAMP();
> -> 882226357
> mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
> -> 875996580
>
> When `UNIX_TIMESTAMP' is used on a `TIMESTAMP' column, the function
> will return the internal timestamp value directly, with no implicit
> "string-to-unix-timestamp" conversion. If you pass an
> out-of-range date to `UNIX_TIMESTAMP()' it will return 0, but
> please note that only basic checking is performed (year 1970-2037,
> month 01-12, day 01-31).
>
> If you want to subtract `UNIX_TIMESTAMP()' columns, you may want to
> cast the result to signed integers. *Note Cast Functions::.

You could write your own C function, wrapped in a UDF that will call
"time()" like this (proper error handling is still missing):

#include <sqludf.h>
#define _XOPEN_SOURCE /* glibc2 needs this */
#include <time.h>

void SQL_API_FN getUnixTime(
SQLUDF_VARCHAR *timestamp,
SQLUDF_BIGINT *seconds,
SQLUDF_NULLIND *timestamp_ind,
SQLUDF_NULLIND *seconds_ind,
SQLUDF_TRAIL_ARGS)
{
if (SQLUDF_NULL(timestamp_ind)) {
// get seconds for current timestamp
*seconds = (SQLUDF_BIGINT)time(NULL);
}
else {
// get seconds for given timestamp
struct tm time;
memset(&time, 0x00, sizeof time);
strptime(timestamp, "%Y-%m-%d-%H:%M:%S", &time);
*seconds = mktime(&time);
}
*seconds_ind = 0;
}

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Knut Stolze <stolze@de.ibm.com> wrote:

> You could write your own C function, wrapped in a UDF that will call
> "time()" like this (proper error handling is still missing):
>
> #include <sqludf.h>
> #define _XOPEN_SOURCE /* glibc2 needs this */
> #include <time.h>
>
> void SQL_API_FN getUnixTime(
> SQLUDF_VARCHAR *timestamp,
> SQLUDF_BIGINT *seconds,
> SQLUDF_NULLIND *timestamp_ind,
> SQLUDF_NULLIND *seconds_ind,
> SQLUDF_TRAIL_ARGS)
> {
> if (SQLUDF_NULL(timestamp_ind)) {
> // get seconds for current timestamp
> *seconds = (SQLUDF_BIGINT)time(NULL);
> }
> else {
> // get seconds for given timestamp
> struct tm time;
> memset(&time, 0x00, sizeof time);
> strptime(timestamp, "%Y-%m-%d-%H:%M:%S", &time);
> *seconds = mktime(&time);
> }
> *seconds_ind = 0;
> }

Sorry, I forgot the CREATE FUNCTION statements:

CREATE FUNCTION unix_timestamp(ts TIMESTAMP)
RETURNS INTEGER
SPECIFIC unix_ts_args
EXTERNAL NAME '<your-lib>!getUnixTime'
LANGUAGE C
PARAMETER STYLE SQL
NOT DETERMINISTIC
NOT FENCED -- only with real error handling!!
THREADSAFE
CALLED ON NULL INPUT
NO SQL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO DBINFO
@

CREATE FUNCTION unix_timestamp()
RETURNS INTEGER
SPECIFIC unix_ts_no_args
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN unix_timestamp(CAST(NULL AS TIMESTAMP))
@

--
Knut Stolze
Information Integration
IBM Germany / University of Jena