PostgreSQL, Perl, DBD and the Insert command with "TO_TIMESTAMP"

Question

I'm programming a script which queries some devices and writes the data to a PostgreSQL database.

The data includes a date which is formatted like 31.12.2015 13:45:00. The database uses the DateType "German" and the column is of the type Timestamp without timezone.

I always get this error message

DBD::Pg::st execute failed: ERROR: invalid input syntax for type timestamp: "TO_TIMESTAMP('19.06.2015 11:24:20','DD.MM.YYYY HH24:MI:SS')::timestamp without time zone" at temp_alcp2e_db.pl line 80, line 289.

I'm using this code, where $date_db has the date value:

$date_db = 'TO_TIMESTAMP(\'' . $date_db . '\',\'DD.MM.YYYY HH24:MI:SS\')::timestamp without time zone';
$stmt = $dbh->prepare("INSERT INTO rifutemp (\"USER_LINK_ID\", \"IP\", \"DATUM\", \"TEMPERATURE\") VALUES (?, ?, ?, ?)");
$stmt->execute($key_bgtr, $key_ip, $date_db, $temperatur) or die $DBI::errstr;

Hopefully, someone can show me what I did wrong.

The PostgreSQL Docs says that the format for german is: "German regional style 17.12.1997 07:37:16.00 PST" so've tried to edit the value into the needed format, which also doesnt work: "DBD::Pg::st execute failed: ERROR: date/time field value out of range: "19.06.2015 12:02:55.00 PST" HINT: Perhaps you need a different "datestyle" setting.", any idea how to solve this problem? — JanAx
– JanAx, Commented Jun 19, 2015 at 10:05
@JanAx try siply select, not insert, maybe problem in column type, not the function: select TO_TIMESTAMP('19.06.2015 11:24:20','DD.MM.YYYY HH24:MI:SS')::timestamp without time zone; to_timestamp --------------------- 2015-06-19 11:24:20 (1 row) Time: 0.292 ms — Vao Tsun
– Vao Tsun, Commented Jun 19, 2015 at 11:32

user464502 · Accepted Answer · 2015-06-20 15:04:40Z

1

The function can (and must) be part of the prepared statement.

Re-write your code as follows:

$stmt =$dbh->prepare(q{
INSERT INTO rifutemp ("USER_LINK_ID","IP","DATUM","TEMPERATURE")        
        VALUES (?, ?, 
        TO_TIMESTAMP(?, 'DD.MM.YYYY HH24:MI:SS')::timestamp without time zone,
        ?)
});

$stmt->execute($key_bgtr,$key_ip,$date_db,$temperatur) or die $DBI::errstr;

answered Jun 20, 2015 at 15:04

user464502

2,4631 gold badge13 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JanAx · Accepted Answer · 2015-06-19 11:49:58Z

0

Ok, I've found my problem / the source of the errors:

To manage the Database visually I'm using the "EMS SQL Manager Lite for PostgreSQL", and this nice GUI tool always sets the DateStyle options to "ISO, MDY".

I've just changed my code like this:

$dbh->do("SET datestyle = 'German'");
$stmt =$dbh->prepare("INSERT INTO rifutemp (\"USER_LINK_ID\",\"IP\",\"DATUM\",\"TEMPERATURE\") 
                            VALUES (?, ?, ?, ?)");
$stmt->execute($key_bgtr,$key_ip,$date_db,$temperatur) or die $DBI::errstr;

and set the $date_db variable to the correct format:

$date_db = Time::Piece->new->strftime('%d.%m.%Y %H:%M:%S');

and now everything works fine!

edited Jun 19, 2015 at 11:49

answered Jun 19, 2015 at 11:38

JanAx

12 bronze badges

2 Comments

Borodin Over a year ago

If the date format really is ISO, MDY then your strftime format is wrong. You should use the solution that @user5018637 offers

Borodin Over a year ago

You can alter the date format with ALTER DATABASE dbase SET datestyle TO "German, DMY", and I imagine there must be an option in EMS Manager to change this

Collectives™ on Stack Overflow

PostgreSQL, Perl, DBD and the Insert command with "TO_TIMESTAMP"

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related