Conversion to date Postgresql

Question

Hello,

I am working with a database that has date information stored in a varchar(50) as in the following example: "2012 December 2 18:37".

I need to do research based on time intervals. However, I am not succeeding in converting this char to date type. I’m trying this way:

to_date(hora, 'YYYY month DD') = '2/12/2015'::date

And I’m getting the bug:

ERROR: invalid value "December " for "Month" DETAIL: The Given value Did not match any of the allowed values for this field.

********** Error **********

ERROR: invalid value "December " for "Month" SQL state: 22007 Detail: The Given value Did not match any of the allowed values for this field.

Answer 1

I thought of two ways to solve your problem:

1- A single function that gives replace in the month and puts a number in place:

CREATE OR REPLACE FUNCTION public.ConverteMesPT (varchar)
RETURNS varchar AS
$body$
SELECT 
Replace(
    Replace(
        Replace(
            Replace(
                Replace(
                    Replace(
                        Replace(
                            Replace(
                                Replace(
                                    Replace(
                                        Replace(
                                            Replace($1,'dezembro','12')
                                            ,'novembro','11')
                                            ,'outubro','10')
                                            ,'setembro','9')
                                            ,'agosto','8')
                                            ,'julho','7')
                                            ,'junho','6')
                                            ,'maio','5')
                                            ,'abril','4')
                                            ,'março','3')
                                            ,'fevereiro','2')
                                            ,'janeiro','1')
$body$
LANGUAGE 'sql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Example:

select to_date(ConverteMesPT('2012 dezembro 2 18:37'::varchar),'YYYY mm DD')

2- Two functions that take care of extracting only the month of the text, and translate it to a corresponding number.

Receives the text, and returns the month number:

CREATE OR REPLACE FUNCTION public.monthFromName (
  varchar
)
RETURNS int AS
$body$
SELECT Case $1
when 'janeiro'then  1 
when 'fevereiro'then 2 
when 'março'then    3 
when 'abril'then    4 
when 'maio'then     5 
when 'junho'then    6 
when 'julho'then    7 
when 'agosto'then   8 
when 'setembro'then 9 
when 'outubro'then  10
when 'novembro'then 11
when 'dezembro'then 12
else NULL
end
$body$
LANGUAGE 'sql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Clears the numbers and points you have in the string to remain only the text:

CREATE OR REPLACE FUNCTION public.limparString (
  varchar
)
RETURNS varchar AS
$body$
SELECT translate($1, '0123456789: ', '')
$body$
LANGUAGE 'sql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Finally, just call the command:

select 
to_date(
    replace('2012 dezembro 2 18:37'::varchar,
            limparString('2012 dezembro 2 18:37'),
            monthFromName(limparString('2012 dezembro 2 18:37'))::varchar),'YYYY mm DD')

In your case:

select 
to_date(
    replace(hora,
            limparString(hora),
            monthFromName(limparString(hora))::varchar),'YYYY mm DD')

See which one suits you best, and I hope I’ve helped.