Postgres invalid date format

Question

Below are some samples of date from a column in my database. Currently, these date values are stored in varchar and I am planning to set these value as timestamp in another table.

How can I set a default rules to cast all the invalid date format to a NULL value or some sort of '00/00/0000 00:00:00" format?

pickup_time 

01/01/2016 07:35:00 
00:00:00
31/12/2015
07:35:00

Expectation:

pickup_time 

01/01/2016 07:35:00 
NULL
31/12/2015 00:00:00
00/00/0000 07:35:00

Ruslan Tolkachev · Accepted Answer · 2017-12-12 06:01:35Z

2

Ok, here is the basic idea, so you need to set a if/else with regex (or something to figure out the pattern of the string) per each case you want to handle.

create or replace function my_super_converter_to_timestamp(arg text)
returns timestamp language plpgsql
as $$
begin
    begin
        -- if(arg like '00:00:00') do something ...
        return arg::timestamp;
    exception when others then
        return null;
    end;
end $$;

answered Dec 12, 2017 at 6:01

Ruslan Tolkachev

6444 silver badges9 bronze badges

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

1 Comment

Derek Lee Over a year ago

that's a great idea! I'll try to write out a function to see if it works. Basically I want to this conversion during my insert to final table from staging using insert into, select

Collectives™ on Stack Overflow

Postgres invalid date format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related