Thread: date format

date format

From
Marc Millas
Date:
Hi,

I would like to load data from a file via file_fdw or COPY.. its a postgres 14 cluster

but.. One date (timestamp) column is written french order and another column is written english order. Data comes from a state owned entity so asking for a normalization may take ages.

obviously I could load as char and then apply an appropriate transformation. no pb.
But is there a direct way to do this ?

thanks


Marc MILLAS
Senior Architect
+33607850334

Re: date format

From
Michael Nolan
Date:
Can you use a CASE statement?  The real issue with date conversion is
not knowing if a value of 02-03-2023 is mm-dd-yyyy or dd-mm-yyyy.

On Wed, Jun 14, 2023 at 11:42 AM Marc Millas <marc.millas@mokadb.com> wrote:
>
> Hi,
>
> I would like to load data from a file via file_fdw or COPY.. its a postgres 14 cluster
>
> but.. One date (timestamp) column is written french order and another column is written english order. Data comes
froma state owned entity so asking for a normalization may take ages. 
>
> obviously I could load as char and then apply an appropriate transformation. no pb.
> But is there a direct way to do this ?
>
> thanks
>
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>



Re: date format

From
"David G. Johnston"
Date:
On Wed, Jun 14, 2023 at 9:42 AM Marc Millas <marc.millas@mokadb.com> wrote:
Hi,

I would like to load data from a file via file_fdw or COPY.. its a postgres 14 cluster

but.. One date (timestamp) column is written french order and another column is written english order. Data comes from a state owned entity so asking for a normalization may take ages.

obviously I could load as char and then apply an appropriate transformation. no pb.
But is there a direct way to do this ?

Probably no - casting formats via locale cannot be specified at that scope when using copy.  Either the cast for a given single setting produces the correct result or it doesn't.  If you need a custom cast like this you have to get away from COPY first.  Usually that is best done after importing data to a temporary table as text.

David J.

Re: date format

From
Marc Millas
Date:

On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Jun 14, 2023 at 9:42 AM Marc Millas <marc.millas@mokadb.com> wrote:
Hi,

I would like to load data from a file via file_fdw or COPY.. its a postgres 14 cluster

but.. One date (timestamp) column is written french order and another column is written english order. Data comes from a state owned entity so asking for a normalization may take ages.

obviously I could load as char and then apply an appropriate transformation. no pb.
But is there a direct way to do this ?

Probably no - casting formats via locale cannot be specified at that scope when using copy.  Either the cast for a given single setting produces the correct result or it doesn't.  If you need a custom cast like this you have to get away from COPY first.  Usually that is best done after importing data to a temporary table as text.

David J.

So, creating a foreign table with varchar type, and then doing the insert as select with the appropriate format.. clear.
somewhat sad as it was a one step process with the former oracle db we get rid off.

Marc 

Re: date format

From
Ron
Date:
On 6/14/23 13:02, Marc Millas wrote:

On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Jun 14, 2023 at 9:42 AM Marc Millas <marc.millas@mokadb.com> wrote:
Hi,

I would like to load data from a file via file_fdw or COPY.. its a postgres 14 cluster

but.. One date (timestamp) column is written french order and another column is written english order. Data comes from a state owned entity so asking for a normalization may take ages.

obviously I could load as char and then apply an appropriate transformation. no pb.
But is there a direct way to do this ?

Probably no - casting formats via locale cannot be specified at that scope when using copy.  Either the cast for a given single setting produces the correct result or it doesn't.  If you need a custom cast like this you have to get away from COPY first.  Usually that is best done after importing data to a temporary table as text.

David J.

So, creating a foreign table with varchar type, and then doing the insert as select with the appropriate format.. clear.
somewhat sad as it was a one step process with the former oracle db we get rid off.

How did Oracle know what format the date was in?

In Postgresql, could you write a simple anonymous procedure that reads the file_fdw table records, does the conversion and then inserts into the destination table?


--
Born in Arizona, moved to Babylonia.

Re: date format

From
Marc Millas
Date:




On Wed, Jun 14, 2023 at 8:15 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 6/14/23 13:02, Marc Millas wrote:

On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Jun 14, 2023 at 9:42 AM Marc Millas <marc.millas@mokadb.com> wrote:
Hi,

I would like to load data from a file via file_fdw or COPY.. its a postgres 14 cluster

but.. One date (timestamp) column is written french order and another column is written english order. Data comes from a state owned entity so asking for a normalization may take ages.

obviously I could load as char and then apply an appropriate transformation. no pb.
But is there a direct way to do this ?

Probably no - casting formats via locale cannot be specified at that scope when using copy.  Either the cast for a given single setting produces the correct result or it doesn't.  If you need a custom cast like this you have to get away from COPY first.  Usually that is best done after importing data to a temporary table as text.

David J.

So, creating a foreign table with varchar type, and then doing the insert as select with the appropriate format.. clear.
somewhat sad as it was a one step process with the former oracle db we get rid off.

How did Oracle know what format the date was in?
when you describe the external file you describe the field format one by one. 

In Postgresql, could you write a simple anonymous procedure that reads the file_fdw table records, does the conversion and then inserts into the destination table?
-> one sql line insert as select  using the to_date() function with appropriate format.

--
Born in Arizona, moved to Babylonia.

Re: date format

From
Marc Millas
Date:



On Wed, Jun 14, 2023 at 8:23 PM Marc Millas <marc.millas@mokadb.com> wrote:




On Wed, Jun 14, 2023 at 8:15 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 6/14/23 13:02, Marc Millas wrote:

On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Jun 14, 2023 at 9:42 AM Marc Millas <marc.millas@mokadb.com> wrote:
Hi,

I would like to load data from a file via file_fdw or COPY.. its a postgres 14 cluster

but.. One date (timestamp) column is written french order and another column is written english order. Data comes from a state owned entity so asking for a normalization may take ages.

obviously I could load as char and then apply an appropriate transformation. no pb.
But is there a direct way to do this ?

Probably no - casting formats via locale cannot be specified at that scope when using copy.  Either the cast for a given single setting produces the correct result or it doesn't.  If you need a custom cast like this you have to get away from COPY first.  Usually that is best done after importing data to a temporary table as text.

David J.

So, creating a foreign table with varchar type, and then doing the insert as select with the appropriate format.. clear.
somewhat sad as it was a one step process with the former oracle db we get rid off.

How did Oracle know what format the date was in?
when you describe the external file you describe the field format one by one. 

In Postgresql, could you write a simple anonymous procedure that reads the file_fdw table records, does the conversion and then inserts into the destination table?
-> one sql line insert as select  using the to_date() function with appropriate format.
https://www.postgresql.org/docs/current/functions-formatting.html

I do know what s in the file, I dont have to guess... 
--
Born in Arizona, moved to Babylonia.

Re: date format

From
Adrian Klaver
Date:
On 6/14/23 11:23, Marc Millas wrote:
> 
> 


Sort of like?:

https://pgloader.readthedocs.io/en/latest/ref/csv.html

date format

column-name [date format 'YYYY-MM-DD HH24-MI-SS.US']

> 
> 
>     In Postgresql, could you write a simple anonymous procedure that
>     reads the file_fdw table records, does the conversion and then
>     inserts into the destination table?
>     -> one sql line insert as select  using the to_date() function with
>     appropriate format.
> 
>     -- 
>     Born in Arizona, moved to Babylonia.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: date format

From
Laurenz Albe
Date:
On Wed, 2023-06-14 at 20:02 +0200, Marc Millas wrote:
> So, creating a foreign table with varchar type, and then doing the insert as select with the appropriate format..
clear.
> somewhat sad as it was a one step process with the former oracle db we get rid off.

It can be a one-step process, if you create a view on the table that uses
to_date() to convert the column to a "date".

Yours,
Laurenz Albe