Conversion to date type PostgreSQL

Question

Conversion to date type PostgreSQL

Navigation

#1 by (1 votes)

0

Hello,

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

I need to do time-based searches. However, I'm not succeeding at 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 error:

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.

sql date postgresql

asked by anonymous 24.05.2017 / 22:00

1 answer

Remove bottom row from jquery tab Import a file containing script and link tags

score 1 · Accepted Answer

I thought of two ways to solve your problem:

1- A single function that gives replace in the month and places 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 are responsible for extracting only the month from the text, and translate it to a corresponding number.

Receives the text, and returns the number of the month:

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 in the string to stay just 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;

By the end, 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 have helped.