Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Lost in DateTime !

by pcouderc (Scribe)
on Jun 13, 2017 at 18:28 UTC ( #1192725=perlquestion: print w/replies, xml ) Need Help??
pcouderc has asked for the wisdom of the Perl Monks concerning the following question:

It starts like a thriller. Paris 23h55. A phone rings. I answer...

asterisk records the call in Postgreql in a "timestamp with timezone" (2017-06-10 21:55:00+02' according to pgadmin).

I try to to print this local time 23:55 with :
my $dbh= DBI->connect( q!dbi:Pg:dbname=asterisk;host=www.ibm.fr!, 'asterisk','secret') or die $DBI::errstr; my $cursor = $dbh->prepare( "SELECT * FROM asterisk_cdr where id=48000;"); $cursor->execute; my @row; if ( @row = $cursor->fetchrow ) { my $Date = DateTime::Format::Pg->parse_timestamp_with_time_zone($row[1]) ; my $Date3= $Date->clone()->set_time_zone('floating')->set_time_zone( 'Europ +e/Paris' ); print "$Date==$Date3",$Date3->time_zone_long_name(), ' ',$Date3->hms,$Date->hms,"\n"; }
I get :
2017-06-10T21:55:00==2017-06-10T21:55:00Europe/Paris 21:55:002 +1:55:00
I have done many other trials but I do not success to print 23:55 !!!

What do I miss, o venerable monks ?

PC

Replies are listed 'Best First'.
Re: Lost in DateTime !
by haukex (Monsignor) on Jun 13, 2017 at 20:40 UTC
    I try to to print this local time 23:55

    If your local time zone is Europe/Paris (currently CEST / +0200), then note that your input time, '2017-06-10 21:55:00+02', is already in that time zone, so when you ->set_time_zone('Europe/Paris'), there is no change in the time zone.

    Now if your input string was '2017-06-10 21:55:00+00' (note the +00, i.e. UTC), then doing a ->set_time_zone('Europe/Paris') on that should give you 2017-06-10 23:55:00 CEST.

      ..indeed i arrived to a similar conclusion, but slowly.. removing the +02 the conversion was made as expected: ie the +02 is the timezone hour modification

      perl -MDateTime -e "@dtarg{qw(year month day hour minute second)}=spli +t/[\s:-]/,'2017-06-10 21:55:00';$dt=DateTime->new(%dtarg); print scal +ar localtime($dt->epoch)" Sat Jun 10 23:55:00 2017

      L*

      There are no rules, there are no thumbs..
      Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.

        There is still a difference though, note that the DateTime object you've built is in the special "floating" time zone, and setting a time zone on it won't adjust the time value:

        use warnings; use strict; use DateTime::Format::Strptime; my $strp = DateTime::Format::Strptime->new( on_error=>'croak', pattern => '%Y-%m-%d %H:%M:%S%z'); my @dt = ( $strp->parse_datetime('2017-06-10 21:55:00+02'), DateTime->new(year=>2017,month=>6,day=>10, hour=>21,minute=>55,second=>0), $strp->parse_datetime('2017-06-10 21:55:00+00'), ); for my $i (0..$#dt) { print "$i: ",$dt[$i]->strftime('%Y-%m-%d %H:%M:%S %Z (%z)'),"\n"; $dt[$i]->set_time_zone('Europe/Paris'); print "$i: ",$dt[$i]->strftime('%Y-%m-%d %H:%M:%S %Z (%z)'),"\n"; } __END__ 0: 2017-06-10 21:55:00 +0200 (+0200) 0: 2017-06-10 21:55:00 CEST (+0200) 1: 2017-06-10 21:55:00 floating (+0000) 1: 2017-06-10 21:55:00 CEST (+0200) 2: 2017-06-10 21:55:00 UTC (+0000) 2: 2017-06-10 23:55:00 CEST (+0200)
      Thank you. Well, I do not know exactly what is my input. I know that is was 23;55 Paris time. And I know that pgadmin displays 2017-06-10 21:55:00+02. I thnk that my input is 2017-06-10 21:55:00+02 and not 2017-06-10 21:55:00+00. My question is now, what should I do to print local time that is 23:55 ... ? .
        I know that is was 23;55 Paris time. And I know that pgadmin displays 2017-06-10 21:55:00+02.

        Then it sounds to me like your time might be stored incorrectly in the DB. Personally I would try to track down the source of this error first, and fix it wherever the times are being put in the DB, and correcting the previously incorrectly stored times directly in the database if possible.

        Looking at the DateTime::Format::Pg docs, there are two methods, ->parse_timestamp_with_time_zone() suitable for columns of type TIMESTAMPTZ, and ->parse_timestamp_without_time_zone(), suitable for columns of type TIMESTAMP. Which do you have?

        Also, have a look at the "Limitations" section in the aforementioned docs - I don't know much about PostgreSQL, but it sounds to me like a discrepancy between the server time zone and your time zone might possibly also be to blame.

        I thnk that my input is 2017-06-10 21:55:00+02

        Have you tried something like print "<<$row[1]>>\n"; to verify?

        From here: I have found a full DateTime solution : ... remove Paris time zone code, by converting to floating, set UTC time code, then convert by setting Paris time code.

        Sorry to be direct but this sounds like a hack that shouldn't be necessary if you correct the above issues. If you don't fix the problem at its source it will bite you elsewhere.

        Thank you all monks for your help. I think that , thanks to you, I have found a full DateTime solution :
        my $Date3= $Date->clone()->set_time_zone('floating')->set_time_zon +e( 'UTC' )->set_time_zone( 'Europe/Paris' );
        That is, remove Paris time zone code, by converting to floating, set UTC time code, then convert by setting Paris time code.
Re: Lost in DateTime !
by 1nickt (Prior) on Jun 13, 2017 at 23:35 UTC

    In my experience it's always best to convert dates and times to epochtime before storing in the DB. Then convert them to some other format when you want to use or display them. As your app grows you'll often find that you need more than one human-readable format, and if you are converting from one format to another at the app level, doing all the conversions from epoch time is easier. Also the dates/times can be sorted numerically.

    Some would say you shouldn't be doing processing in your app that could be handled at the DB layer. Yes, it's true that your DB can handle dates, and probably even provides date/time functions. But you have Perl! Store the simplest representation of your data in the DB and do your processing in Perl, and you will die a happy monk.

    Hope this helps!


    The way forward always starts with a minimal test.
      Thank you, please see just above.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1192725]
Approved by herveus
Front-paged by Corion
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (8)
As of 2017-11-20 07:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:













    Results (285 votes). Check out past polls.

    Notices?