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

jtobelem
jtobelem Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered, Neuron 2022 Posts: 19 ✭✭✭✭✭
edited July 16 in Using Dataiku

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.

Tagged:

Best Answers

  • AndrewM
    AndrewM Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer Posts: 20 Dataiker
    Answer ✓

    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 '

    Thank you

    Andrew M

  • jtobelem
    jtobelem Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered, Neuron 2022 Posts: 19 ✭✭✭✭✭
    Answer ✓

    Hello Andrew,

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

    Regards

Setup Info
    Tags
      Help me…