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

Postgresql – Convert text column with timestamp literals in non-standard format to timestamptz

I am getting raw data from a customer which I am loading into a db using a script. As of now, I am storing everything as strings. I have data in this format: 18.02.2024 00:06:09.125.

I want to convert the table column to type timestamp with time zone, but run into errors with the format.
One of the queries I tried:

ALTER TABLE <table Name> 
ALTER COLUMN <column name> TYPE timestamp with time zone USING <column name>::timestamp with time zone

Getting below error:

Error in query: ERROR: date/time field value out of range: "18.02.2024 10:10:40.409"HINT: Perhaps you need a different "datestyle" setting.

I have tried applying date format in the excel/csv file directly, even that failed.

Tags:

3

Answers


  1. 0

    Let us know the database you are using.

    Here is an example I used in Postgres

    Fiddle contains steps to create a new column with Timestamp datatype, update the new column based on old column and then drop the old column.

      SELECT  TO_TIMESTAMP('01.01.2024 00:06:09.125', 'DD.MM.YYYY HH24:MI:SS.MS')
    Login or Signup to reply.
  2. 0

    You will not be able to alter the column if there are already data in it which is not in the correct format.

    I suggest you create another column and then run an update to "copy" to this new column in the correct format. You’ll then be able to rename if you want.

    samhita’s answer is only a hint to what you should be doing.

    Run this update:

    UPDATE <your table> set <new column> = TO_TIMESTAMP(<column name>, 'DD.MM.YYYY HH24:MI:SS.MS') WHERE 1

    This should correctly populate your new column in date/time format.

    Login or Signup to reply.
  3. 0

    Your timestamp literals are in default German (and many other countries) format ‘DD.MM.YYYY’. Best use ISO format ‘YYYY-MM-DD’ everywhere, but for your current predicament:

    The simplest and fastest way is probably to set datestyle accordingly. Then the command you already tried just works. Either temporarily for the session to convert your timestamp literals, or permanently in postgresql.conf, if that’s what you really need.

    First check with:

    SHOW datestyle;

    In your session:

    SET datestyle = 'ISO, DMY';

ALTER TABLE <table name> 
ALTER COLUMN <column name> TYPE timestamptz USING <column name>::timestamptz

-- RESET datestyle;  -- ?

    db-fiddle

    The manual advises:

    to_timestamp and to_date exist to handle input formats that cannot
    be converted by simple casting. For most standard date/time formats,
    simply casting the source string to the required data type works, and
    is much easier.

    … assuming datestyle is set appropriately.

    Since you are converting a timestamp without offset (timestamp literal, not timestamptz) the conversion defaults to the timezone setting of the current session. You may want to set that accordingly, too.

    Related:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search