Professional Web Applications Themes

Does this function return the seconds since 1970-01-01? - IBM DB2

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) ;...

  1. #1

    Default Does this function return the seconds since 1970-01-01?

    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 Guest

  2. #2

    Default Re: Does this function return the seconds since 1970-01-01?

    Zsolt Koppany <com> wrote:
     

    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
    Knut Guest

  3. #3

    Default Re: Does this function return the seconds since 1970-01-01?

    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" <ibm.com> schrieb im Newsbeitrag
    news:bmdu9g$21o$rz.uni-jena.de... 
    >
    > 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[/ref]


    Zsolt Guest

  4. #4

    Default Re: Does this function return the seconds since 1970-01-01?

    Zsolt Koppany <com> wrote:
     

    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/setTemplate:singlecontent/contentTypeA/conWebDoc/contentId/347
    http://www.boulder.nist.gov/timefreq/pubs/bulletin/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 Guest

  5. #5

    Default Re: Does this function return the seconds since 1970-01-01?

    Knut Stolze wrote: 
    >
    > 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.[/ref]

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

    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: net, ibm.com
    Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

    Jonathan Guest

  6. #6

    Default Re: Does this function return the seconds since 1970-01-01?

    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 doentation):

    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" <net> schrieb im Newsbeitrag
    news:3YKib.463$news.pas.earthlink.net... 
    > >
    > > Lepa seconds are used to compensate for the difference of Earth's[/ref][/ref]
    rotation [/ref]
    into 
    >
    > You probably don't want to take them into account - POSIX.1 explicitly
    > does not, so the operating system doesn't either (in general).
    > [/ref]
    introduced. 


    Zsolt Guest

  7. #7

    Default Re: Does this function return the seconds since 1970-01-01?

    Zsolt Koppany <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;
    }

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

  8. #8

    Default Re: Does this function return the seconds since 1970-01-01?

    Knut Stolze <ibm.com> wrote:
     

    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
    Knut Guest

Similar Threads

  1. Replies: 1
    Last Post: June 3rd, 09:03 PM
  2. #24967 [Bgs]: fopen when placed within a function causes function to return
    By laurie at oneuponedown dot co dot uk in forum PHP Development
    Replies: 1
    Last Post: August 7th, 12:12 PM
  3. #24967 [NEW]: fopen when placed within a function causes function to return
    By laurie at oneuponedown dot co dot uk in forum PHP Development
    Replies: 0
    Last Post: August 6th, 09:14 PM
  4. function to get time in seconds and milliseconds
    By John Smith in forum UNIX Programming
    Replies: 17
    Last Post: July 7th, 02:56 PM
  5. Function/Global var to return name of calling function?
    By Daniel Hansen in forum PHP Development
    Replies: 3
    Last Post: July 6th, 12:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139