NumberFormatException: Trailing junk on timestamp: ':21' with pgsql

Solved!
jtobelem
Level 3
NumberFormatException: Trailing junk on timestamp: ':21' with pgsql

Hi,

I got this exception :

Failed to read data from table, caused by: PSQLException: Bad value for type timestamp : 1853-01-01 04:38:21+00:09:21, caused by: NumberFormatException: Trailing junk on timestamp: ':21'
HTTP code: 500, type: com.dataiku.dip.exceptions.DataStoreIOException

while trying the Hands-On: "In-Database Charts" from the "Dataiku DSS & SQL" course.

I run successfully a prepare recipe on a dataset named "customer_stacked"

But the result dataset "customer_stacked_prepared" is not build due to this error. I can not investigate this error as the prepare recipe runs ok and I don't know where to look at.

0 Kudos
2 Solutions
AndrewM
Dataiker

Hello,

This can happen with a timestamp that falls outside of a valid range, usually due to a timezone offset. You can set timezone = 'UTC' in postgresql.conf and restart Postgres to force PG to recognize this as UTC time, which should cause the timestamp to fall back into a valid range. 

If you have psql on your machine you should be able to run the following command and find the location of the conf file: 

psql -U postgres -c 'SHOW config_file'

Thank you

Andrew M

View solution in original post

jtobelem
Level 3
Author

Hello Andrew,

Thank you for your respone! it works fine with the parameter timezone set to 'UTC'.

Regards

 

View solution in original post

0 Kudos
2 Replies
AndrewM
Dataiker

Hello,

This can happen with a timestamp that falls outside of a valid range, usually due to a timezone offset. You can set timezone = 'UTC' in postgresql.conf and restart Postgres to force PG to recognize this as UTC time, which should cause the timestamp to fall back into a valid range. 

If you have psql on your machine you should be able to run the following command and find the location of the conf file: 

psql -U postgres -c 'SHOW config_file'

Thank you

Andrew M

jtobelem
Level 3
Author

Hello Andrew,

Thank you for your respone! it works fine with the parameter timezone set to 'UTC'.

Regards

 

0 Kudos