PostgreSQL Question posted in
The official documentation can be found here.

datetime2 insertion in postgresql

good morning,

I’m working on a datalake that retrieves data from sqlserver, transforms it with dbt and then inserts it into postgresql.

The format of my source data has changed (the software editor has made changes in sqlserver) and I now have time datetime2 (0) or datetime2 (7) instead of datetime. Since this change, dbt has been giving me errors for each date column that was previously inserted as a timestamp, see first screen : invalid input syntax for type date: "Mar 17 2021 08:21:19:0000000AM".

Especially since in my sqlserver table the writing is not the same as the insertion in postgresql. In sqlserver I have YYYY-MM-DD HH:MM:SS.FFFFFFF instead of the month on 3 letters in postgresql visible in the error. I have to cast in varchar to avoid insertion errors, but then I can’t calculate durations (previously just end time – start time). I’m looking for a way to cleanly cast the displayed format of datetime(2) to timestamp

I tried
–TO_TIMESTAMP("date_debut", ‘YYYY-MM-DD HH:MM:SS’) and i get invalid value "Oct " for "YYYY"

and date_debut::timestamp

Any suggestions?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you for your answer, i didn't knew the Mon DD YYYY syntax Here is what i'm finally doing and it does work :

    TO_TIMESTAMP(SUBSTRING(CAST("date_debut" AS VARCHAR(30)) FROM 1 FOR 20), 'Mon DD YYYY HH12:MI:SS')::timestamp AS date_debut

  2. 0

    The format is not correct, you mention the ISO format but that will be the output from to_timestamp() anyway. You should mention how your input looks like. This one works for me:

    SELECT to_timestamp('Mar 17 2021 08:21:19:0000000AM'
                   ,'Mon DD YYYY HH12:MI:SS:USAM');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search