http://qs321.pair.com?node_id=608419

kurre_vaxholm has asked for the wisdom of the Perl Monks concerning the following question:

Hi there.. I have a problem. I query a Sybase database and get the date back in epoch format, but since I am on 32 bit the latest date that I can convert with gmtime is 2038 and that's not good enough (I have a loan that matures 2087). I have had a look on Date::Manip and Date::Calc but the parts that handles epoch times seems to rely on the same libs that gmtime does. Is this correct?? To sum it up: I need to convert 3700771200 to a "realdate". BR

Replies are listed 'Best First'.
Re: Converting epoch times after 2038
by derby (Abbot) on Apr 05, 2007 at 12:19 UTC

    I query a Sybase database and get the date back in epoch format

    Why don't you let Sybase do the conversion for you:

    select convert(varchar(12), mydate, 3) from table
    That will convert a date file into Sybase's format 3 which is dd/mm/yy. here's the list of formats.

    -derby
      Hi derby. Well I have built a framwork where you can define your dateformat and alot of other formats for floats integers etc, in a template. If I shall be able to easily redefine my dateformat it needs to be in epoch since that's what strftime uses. But right now i have decided to convert the date in the sp and redo my frame when I have the time. Cheers
Re: Converting epoch times after 2038
by Moron (Curate) on Apr 05, 2007 at 12:32 UTC
    If this is the XY problem I think it is, you could get Sybase to split the dateparts for you in advance using the Transact-SQL datepart function, e.g.:
    SELECT datepart(yy,attr_val), datepart(mm,attr_val), datepart(dd,attr_val) FROM date_attribute_values WHERE instrument_id = 12345 AND attr_id = 'MATURITY'

    -M

    Free your mind

Re: Converting epoch times after 2038
by zer (Deacon) on Apr 05, 2007 at 06:35 UTC
    are you using DateTime::Format::Epoch or the default one?
      I am using the default one. Do you think that DateTime::Format::Epoch will do the trick?? I'll look it up on CPAN...

        Apparently, it does:

        use DateTime qw( ); use DateTime::Format::Epoch qw( ); my $epoch = DateTime->new( year => 1970, month => 1, day => 1 ); my $formatter = DateTime::Format::Epoch->new( epoch => $epoch ); my $dt = $formatter->parse_datetime( 3700771200 ); print( $dt->strftime( '%x %X' ), "\n" ); # Apr 10, 2087 12:00:00 AM

        Update: Better yet:

        use DateTime::Format::Epoch::Unix qw( ); my $dt = DateTime::Format::Epoch::Unix->parse_datetime( 3700771200 ); print( $dt->strftime( '%x %X' ), "\n" ); # Apr 10, 2087 12:00:00 AM
Re: Converting epoch times after 2038
by ambrus (Abbot) on Apr 05, 2007 at 07:51 UTC

    Perl on a 64-bit system can do it. Here's the answer from an x86_64 perl-5.8.8:

    $ perl -we 'warn "".localtime(1<<33), "\n"' Wed Mar 16 13:56:32 2242

    Update: Time::Local doesn't work after 2038 though.

        Be careful with that on 32-bit platforms, since on 32-bit platforms 1 << 33 will be 2

        ... when perl is not built with 64-bit integer support (here it is "v5.8.8 built for i386-freebsd-64int" (32 bit architecture)) ...

        perl -we 'print map { sprintf "%d %d\n" , $_ , ( 1 << $_ ) } ( 30 .. +34 , 60 .. 65 ) ' 30 1073741824 31 2147483648 32 4294967296 33 8589934592 34 17179869184 60 1152921504606846976 61 2305843009213693952 62 4611686018427387904 63 -9223372036854775808 64 1 65 2

      Hmmmm...

      I'd be very unhappy to know that the same Perl version would behave differently on a 64-bit system then on a 32-bit system.

      I would consider this a bug - not a feature. Am I missing something in that regard ?

      I imagine using an 64-bit system in development, building an app, letting it out and then get customers complaints about calendar mishappenings - due to the fact that they use 32-bit systems. Scary.
        you consider a bug that perl uses libc? hmhmmhm... weird...
        daniel

        I too would consider it more a bug than a feature, nonetheless, it seems to be true. I'm getting 7pm Dec 31, 1969 with that one-liner. This is perl, v5.8.8 built for i386-freebsd-64int, running on a 32-bit x86 architecture (Celeron IIRC).

        One supposes this means perl is relying too heavily on the underlying C library for certain things.

        However, the DateTime::Format::Epoch::Unix code that ikegami posted works fine, so maybe the moral of the story is to always use DateTime modules when you're handling dates and times. (I've been doing that for years anyway, because it's better in other ways. A 2038 bug in localtime would just be the icing on the cake.)

        -- 
        We're working on a six-year set of freely redistributable Vacation Bible School materials.
Re: Converting epoch times after 2038
by mattr (Curate) on Apr 08, 2007 at 00:31 UTC
    Hi,

    How about trying Time::TAI64. I have not tried it myself but it looks interesting.

    The only thing is that TAI does not use leap seconds. However this is not a big problem it would seem, neither for you or for astronomers who might use the attosecond based extension.

    Perhaps so long as you set the date of a bond to be NOON every day or any other time but say 00:00:00 midnight, there will be no problem. TAI uses leap second tables (for those known in the past) but even so it would only be probably a few seconds difference over 50 years (see the cr.yp.to link below).

    Also check out libtai info at cr.py.to and MJD post in 2000 (met him in Tokyo for YAPC::Asia a few days ago!! Hi MJD!)

Re: Converting epoch times after 2038
by kurre_vaxholm (Acolyte) on Apr 10, 2007 at 06:58 UTC
    Thanks everyone, for your input. Since i am running out of time I have decided to modify my Sybase sp, so it will return the date as a string. Later when I have time i will try to modify my framework so it will use another time module. But until then I'll stick to the TSQL convert function. Cheers