Re^3: Lost in DateTime !

[haukex]

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.

[pcouderc]

Please note that I do N_O_T write in the DB. I R_E_A_D it : "asterisk" (software for PABX, see WK) writes for me. My DB is soon written.
About ->parse_timestamp_with_time_zone(), pease see above to see my code. In fact, I have tried all...

[huck]

Please note that I do N_O_T write in the DB. I R_E_A_D it : "asterisk" (software for PABX, see WK) writes for me.

That does not imply that asterisk is properly set up. at https://wiki.asterisk.org/wiki/display/AST/PostgreSQL+CDR+Backend it implies that the column should be "calldate timestamp NOT NULL" but it seems yours might be "calldate timestamp with timezone NOT NULL". If "asterisk" does not supply time zone information when inserting the data it is quite possible that 23:55 is being converted to GMT prior to insertion but postgres is thinking it is in paris time and converts it to GMT AGAIN to store in its database, which is then converted to 21:55+02 on output to you.

I agree that the real solution is to fix whatever is writing into the database

[poj]

What does this query return ?

#!/usr/bin/perl
use strict;
use DBD::Pg;

my $dsn = "dbi:Pg:dbname=asterisk;host=www.ibm.fr";         
my $dbh = DBI->connect($dsn,'asterisk','secret') or die $DBI::errstr;;
my $sql = 'SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP';
print join "\n",$dbh->selectrow_array($sql);
$dbh->disconnect;

poj