pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Date format
31+ messages / 18 participants
[nested] [flat]

* Date format
@ 1998-12-11 16:38  Constantine Manoussakis <cmanoussakis@yahoo.com>
  0 siblings, 0 replies; 31+ messages in thread

From: Constantine Manoussakis @ 1998-12-11 16:38 UTC (permalink / raw)
  To: pgsql-general

Dear People,
I am a new Linux user and an even newer SQL database user. The problem
that I am having is that I cannot enter a date into a table that I
have created with date and datetime colums (i.e create table
daten(dat1 date, dat2 datetime).
The question essentially is how do I INSERT a date 23/10/1997 into my
table?

Thanks in advance

my email is: cmanoussakis@yahoo.com


_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com




^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Date format
@ 2000-01-14 16:19  Patrick Welche <prlw1@newn.cam.ac.uk>
  0 siblings, 0 replies; 31+ messages in thread

From: Patrick Welche @ 2000-01-14 16:19 UTC (permalink / raw)
  To: pgsql-general

Someone posted a message here asking how you set the date style. We went
through  
- environment variable PGDATESTYLE
- SET DateStyle TO
- the -e flag to the backend   

but of course if you are using ISO it isn't necessary!! There is no
ambiguity in

test=> create table tab (t datetime); 
CREATE
test=> insert into tab values('2000-1-2');
INSERT 416553 1
test=> select * from tab;
              t
------------------------------
 Sun 02 Jan 00:00:00 2000 GMT
(1 row)

The -e exists because there would be ambiguity between European/US, but with
year first, it must be yyyy-mm-dd (at least for sufficiently large years).

Sorry I can't remember your email address.

Cheers,

Patrick



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* date format
@ 2003-04-02 22:59  scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 31+ messages in thread

From: scott.marlowe @ 2003-04-02 22:59 UTC (permalink / raw)
  To: pgsql-general

The SQL 92 spec is quite specific that there is an exact order for date 
and it is yyyymmdd period, and that these are constrained by the rules of 
a well formed gregorian calendar (i.e. no 20020231 allowed)

Seeing as how putting in 2002-22-04 and 2002-04-22 on my box results in 
two inputs of april 22nd 2002, it's obvioue postgresql is trying to make 
data fit that should result in a constraint violation.

I found this on p 110-112 or so of the SQL92 draft in text that's so 
common.  it's section 6.1 or so.




^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2003-04-03 05:57  Tony Grant <tony@tgds.net>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 0 replies; 31+ messages in thread

From: Tony Grant @ 2003-04-03 05:57 UTC (permalink / raw)
  To: pgsql-general

On Thu, 2003-04-03 at 00:59, scott.marlowe wrote:
> The SQL 92 spec is quite specific that there is an exact order for date 
> and it is yyyymmdd period, and that these are constrained by the rules of 
> a well formed gregorian calendar (i.e. no 20020231 allowed)
> 
> Seeing as how putting in 2002-22-04 and 2002-04-22 on my box results in 
> two inputs of april 22nd 2002, it's obvioue postgresql is trying to make 
> data fit that should result in a constraint violation.

I can confirm that that inserting dates via JSP + the JDBC driver into
Postgresql does exactly this.

If you want a nice interface that inserts dates in the DD-MM-YYYY format
you will have to write a nice little Javascript function that puts the
date back into the right order before inserting into the database.

SELECT to_char(dateexpofin, 'DD-MM-YYYY') as end, dateexpofin
ORDER BY dateexpofin DESC

will write out the date in "normal" European order and put the latest
date on top of the list.

I was stuck on dates a while back.

Cheers

Tony Grant
-- 
www.tgds.net Library management software toolkit, 
redhat linux on Sony Vaio C1XD, 
Dreamweaver MX with Tomcat and PostgreSQL




^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* date format
@ 2005-12-08 17:44  David Rysdam <drysdam@ll.mit.edu>
  0 siblings, 1 reply; 31+ messages in thread

From: David Rysdam @ 2005-12-08 17:44 UTC (permalink / raw)
  To: pgsql-general

I could swear (but I don't think I can prove at this point) that 8.0 
beta3 returned timestamps with milliseconds, like this:

YYYY-MM-DD HH24:MI:SS.MS

But 8.1 isn't doing that.  I see functions to format the date, but that 
would require me to change all my existing SQL to specifically ask for 
the milliseconds.  There's also the "set datestyle" thing, but the 
granularity doesn't exist to specify an exact format such as the above.  
Is there a way to tell postgres *exactly* what I want the *default* date 
output format to be?



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2005-12-08 18:02  David Rysdam <drysdam@ll.mit.edu>
  parent: David Rysdam <drysdam@ll.mit.edu>
  0 siblings, 1 reply; 31+ messages in thread

From: David Rysdam @ 2005-12-08 18:02 UTC (permalink / raw)
  To: ; +Cc: pgsql-general

David Rysdam wrote:

> I could swear (but I don't think I can prove at this point) that 8.0 
> beta3 returned timestamps with milliseconds, like this:
>
> YYYY-MM-DD HH24:MI:SS.MS
>
> But 8.1 isn't doing that.  I see functions to format the date, but 
> that would require me to change all my existing SQL to specifically 
> ask for the milliseconds.  There's also the "set datestyle" thing, but 
> the granularity doesn't exist to specify an exact format such as the 
> above.  Is there a way to tell postgres *exactly* what I want the 
> *default* date output format to be?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>
Nevermind, the documentation just seems to be unclear.  I do get .MS 
back in ISO format (despite that not being shown as part of the ISO 
format).  It's just that none of my dates have milliseconds on them, 
which is simply a bug I'll have to find.



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2005-12-08 18:12  David Rysdam <drysdam@ll.mit.edu>
  parent: David Rysdam <drysdam@ll.mit.edu>
  0 siblings, 0 replies; 31+ messages in thread

From: David Rysdam @ 2005-12-08 18:12 UTC (permalink / raw)
  To: ; +Cc: pgsql-general

David Rysdam wrote:

> David Rysdam wrote:
>
>> I could swear (but I don't think I can prove at this point) that 8.0 
>> beta3 returned timestamps with milliseconds, like this:
>>
>> YYYY-MM-DD HH24:MI:SS.MS
>>
>> But 8.1 isn't doing that.  I see functions to format the date, but 
>> that would require me to change all my existing SQL to specifically 
>> ask for the milliseconds.  There's also the "set datestyle" thing, 
>> but the granularity doesn't exist to specify an exact format such as 
>> the above.  Is there a way to tell postgres *exactly* what I want the 
>> *default* date output format to be?
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>
>>
> Nevermind, the documentation just seems to be unclear.  I do get .MS 
> back in ISO format (despite that not being shown as part of the ISO 
> format).  It's just that none of my dates have milliseconds on them, 
> which is simply a bug I'll have to find.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
OK, sorry to flood on this, but there is still a problem.  If I have no 
milliseconds, I'd like to get back ".000" at the end of the output.  
Since MS are indeed part of the ISO format, why don't get I get zeros?  
Alternatively, if MS aren't part of the ISO format, why do I get non-zeros?



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* date format
@ 2007-03-04 12:47  Garry Saddington <garry@schoolteachers.co.uk>
  0 siblings, 3 replies; 31+ messages in thread

From: Garry Saddington @ 2007-03-04 12:47 UTC (permalink / raw)
  To: pgsql-general

I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still 
rendered in the format (y,m,d) . How can I change this behaviour? 
regards
garry



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 12:58  Jorge Godoy <jgodoy@gmail.com>
  parent: Garry Saddington <garry@schoolteachers.co.uk>
  2 siblings, 1 reply; 31+ messages in thread

From: Jorge Godoy @ 2007-03-04 12:58 UTC (permalink / raw)
  To: Garry Saddington <garry@schoolteachers.co.uk>; +Cc: pgsql-general

Garry Saddington <garry@schoolteachers.co.uk> writes:

> I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still 
> rendered in the format (y,m,d) . How can I change this behaviour? 

ISO means y-m-d...  

-- 
Jorge Godoy      <jgodoy@gmail.com>



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 15:28  Raymond O'Donnell <rod@iol.ie>
  parent: Jorge Godoy <jgodoy@gmail.com>
  0 siblings, 1 reply; 31+ messages in thread

From: Raymond O'Donnell @ 2007-03-04 15:28 UTC (permalink / raw)
  To: pgsql-general

On 04/03/2007 12:58, Jorge Godoy wrote:
> Garry Saddington <garry@schoolteachers.co.uk> writes:
> 
>> I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still 
>> rendered in the format (y,m,d) . How can I change this behaviour? 
> 
> ISO means y-m-d...  

The default postgresql.conf that is installed has

       datestyle = iso,mdy

though commented out - on a new installation I uncomment this line as a 
matter of course and change it to "iso,dmy" and it works fine.

Are you sure that PostgreSQL is presenting the dates in the wrong 
format? Could there be another layer that's swapping the month and year 
around? - try SELECTing some date values from psql and see what you get.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 16:16  Garry Saddington <garry@schoolteachers.co.uk>
  parent: Raymond O'Donnell <rod@iol.ie>
  0 siblings, 1 reply; 31+ messages in thread

From: Garry Saddington @ 2007-03-04 16:16 UTC (permalink / raw)
  To: pgsql-general; rod@iol.ie

On Sunday 04 March 2007 15:28, Raymond O'Donnell wrote:
> On 04/03/2007 12:58, Jorge Godoy wrote:
> > Garry Saddington <garry@schoolteachers.co.uk> writes:
> >> I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is
> >> still rendered in the format (y,m,d) . How can I change this behaviour?
> >
> > ISO means y-m-d...
>
> The default postgresql.conf that is installed has
>
>        datestyle = iso,mdy
>
> though commented out - on a new installation I uncomment this line as a
> matter of course and change it to "iso,dmy" and it works fine.
>
> Are you sure that PostgreSQL is presenting the dates in the wrong
> format? Could there be another layer that's swapping the month and year
> around? - try SELECTing some date values from psql and see what you get.
>
I tried pgAdmin3 on windows to run 'select now()'  and it still does not 
return the correct datestyle.
Regards
Garry




^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 16:16  Raymond O'Donnell <rod@iol.ie>
  parent: Garry Saddington <garry@schoolteachers.co.uk>
  0 siblings, 2 replies; 31+ messages in thread

From: Raymond O'Donnell @ 2007-03-04 16:16 UTC (permalink / raw)
  To: pgsql-general

On 04/03/2007 16:16, Garry Saddington wrote:

> I tried pgAdmin3 on windows to run 'select now()'  and it still does not 
> return the correct datestyle.

Probably a silly question, but did you remember to restart the server 
after changing the datestyle setting?

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 17:05  Garry Saddington <garry@schoolteachers.co.uk>
  parent: Raymond O'Donnell <rod@iol.ie>
  1 sibling, 1 reply; 31+ messages in thread

From: Garry Saddington @ 2007-03-04 17:05 UTC (permalink / raw)
  To: pgsql-general; rod@iol.ie

On Sunday 04 March 2007 16:16, Raymond O'Donnell wrote:
> On 04/03/2007 16:16, Garry Saddington wrote:
> > I tried pgAdmin3 on windows to run 'select now()'  and it still does not
> > return the correct datestyle.
>
> Probably a silly question, but did you remember to restart the server
> after changing the datestyle setting?
yes
regards
garry
> Ray.
>
> ---------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> ---------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 17:09  Martijn van Oosterhout <kleptog@svana.org>
  parent: Garry Saddington <garry@schoolteachers.co.uk>
  0 siblings, 1 reply; 31+ messages in thread

From: Martijn van Oosterhout @ 2007-03-04 17:09 UTC (permalink / raw)
  To: Garry Saddington <garry@schoolteachers.co.uk>; +Cc: pgsql-general; rod@iol.ie

On Sun, Mar 04, 2007 at 05:05:44PM +0000, Garry Saddington wrote:
> On Sunday 04 March 2007 16:16, Raymond O'Donnell wrote:
> > On 04/03/2007 16:16, Garry Saddington wrote:
> > > I tried pgAdmin3 on windows to run 'select now()'  and it still does not
> > > return the correct datestyle.
> >
> > Probably a silly question, but did you remember to restart the server
> > after changing the datestyle setting?
> yes
> regards

Can you show us your exact output so we don't have to guess? I get
this:

# set datestyle=iso,dmy;
SET
# select now();
              now
-------------------------------
 2007-03-04 18:07:54.626267+01
(1 row)

Which is precisely what I expect.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 18:32  Joshua D. Drake <jd@commandprompt.com>
  parent: Garry Saddington <garry@schoolteachers.co.uk>
  2 siblings, 0 replies; 31+ messages in thread

From: Joshua D. Drake @ 2007-03-04 18:32 UTC (permalink / raw)
  To: Garry Saddington <garry@schoolteachers.co.uk>; +Cc: pgsql-general

Garry Saddington wrote:
> I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still 
> rendered in the format (y,m,d) . How can I change this behaviour? 
>   
Did you restart PostgreSQL after making the change? You can also set it 
on the fly per session.

Joshua D. Drake




> regards
> garry
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>
>   




^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 18:56  Garry Saddington <garry@schoolteachers.co.uk>
  parent: Martijn van Oosterhout <kleptog@svana.org>
  0 siblings, 1 reply; 31+ messages in thread

From: Garry Saddington @ 2007-03-04 18:56 UTC (permalink / raw)
  To: Martijn van Oosterhout <kleptog@svana.org>; +Cc: pgsql-general; rod@iol.ie

On Sunday 04 March 2007 17:09, Martijn van Oosterhout wrote:
> On Sun, Mar 04, 2007 at 05:05:44PM +0000, Garry Saddington wrote:
> > On Sunday 04 March 2007 16:16, Raymond O'Donnell wrote:
> > > On 04/03/2007 16:16, Garry Saddington wrote:
> > > > I tried pgAdmin3 on windows to run 'select now()'  and it still does
> > > > not return the correct datestyle.
> > >
> > > Probably a silly question, but did you remember to restart the server
> > > after changing the datestyle setting?
> >
> > yes
> > regards
>
> Can you show us your exact output so we don't have to guess? I get
> this:
>
> # set datestyle=iso,dmy;
> SET
> # select now();
>               now
> -------------------------------
>  2007-03-04 18:07:54.626267+01
> (1 row)
>
> Which is precisely what I expect.
Which is precisely what I get, but I want  04-03-2007.
regards
Garry



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 19:26  Raymond O'Donnell <rod@iol.ie>
  parent: Garry Saddington <garry@schoolteachers.co.uk>
  0 siblings, 1 reply; 31+ messages in thread

From: Raymond O'Donnell @ 2007-03-04 19:26 UTC (permalink / raw)
  To: pgsql-general

On 04/03/2007 18:56, Garry Saddington wrote:
>> # select now();
>>               now
>> -------------------------------
>>  2007-03-04 18:07:54.626267+01
>> (1 row)
>>
>> Which is precisely what I expect.
> Which is precisely what I get, but I want  04-03-2007.

Ahh! Then you need to use to_char(). The datestyle setting in 
postgresql.conf differentiates *only* between European and American date 
formats - i.e. the relative order of day and month. It doesn't set the 
exact output format.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-04 19:34  Raymond O'Donnell <rod@iol.ie>
  parent: Raymond O'Donnell <rod@iol.ie>
  0 siblings, 0 replies; 31+ messages in thread

From: Raymond O'Donnell @ 2007-03-04 19:34 UTC (permalink / raw)
  To: pgsql-general

On 04/03/2007 19:26, Raymond O'Donnell wrote:

> Ahh! Then you need to use to_char(). The datestyle setting in 

http://www.postgresql.org/docs/8.2/static/functions-formatting.html

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-05 06:14  Tino Wildenhain <tino@wildenhain.de>
  parent: Garry Saddington <garry@schoolteachers.co.uk>
  2 siblings, 0 replies; 31+ messages in thread

From: Tino Wildenhain @ 2007-03-05 06:14 UTC (permalink / raw)
  To: Garry Saddington <garry@schoolteachers.co.uk>; +Cc: pgsql-general

Garry Saddington schrieb:
> I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still 
> rendered in the format (y,m,d) . How can I change this behaviour? 
> regards
> garry
>
See my answer on the Zope list. The style however influences the way
dates are parsed by default, so you need to take care when you
feed dates from your application to database.
You should also use to_date() to make it explicit.

Regards
Tino



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-05 10:04  Peter Eisentraut <peter_e@gmx.net>
  parent: Raymond O'Donnell <rod@iol.ie>
  1 sibling, 1 reply; 31+ messages in thread

From: Peter Eisentraut @ 2007-03-05 10:04 UTC (permalink / raw)
  To: pgsql-general; rod@iol.ie

Am Sonntag, 4. März 2007 17:16 schrieb Raymond O'Donnell:
> Probably a silly question, but did you remember to restart the server
> after changing the datestyle setting?

You don't need to do that.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-05 12:16  Joshua D. Drake <jd@commandprompt.com>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 31+ messages in thread

From: Joshua D. Drake @ 2007-03-05 12:16 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-general; rod@iol.ie

Peter Eisentraut wrote:
> Am Sonntag, 4. März 2007 17:16 schrieb Raymond O'Donnell:
>   
>> Probably a silly question, but did you remember to restart the server
>> after changing the datestyle setting?
>>     
>
> You don't need to do that.
>   
I didn't really explain my point here. You an use datestyle from a 
session, or you can reload the server.
You do not need to *restart*. However, he didn't mention if he had done 
any of that. He just said he set
it which doesn't really mean anything if he didn't change it from the 
setting or reload the server.

J







^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2007-03-05 12:29  Raymond O'Donnell <rod@iol.ie>
  parent: Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 0 replies; 31+ messages in thread

From: Raymond O'Donnell @ 2007-03-05 12:29 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-general

On 05/03/2007 12:16, Joshua D. Drake wrote:

> I didn't really explain my point here. You an use datestyle from a 
> session, or you can reload the server. You do not need to *restart*.

Yes, that's what I was getting at as well - I was in a hurry at the time
and "restart" was the wrong word to use.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* date format
@ 2023-06-14 16:42  Marc Millas <marc.millas@mokadb.com>
  0 siblings, 2 replies; 31+ messages in thread

From: Marc Millas @ 2023-06-14 16:42 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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
www.mokadb.com

^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2023-06-14 17:05  Michael Nolan <htfoot@gmail.com>
  parent: Marc Millas <marc.millas@mokadb.com>
  1 sibling, 0 replies; 31+ messages in thread

From: Michael Nolan @ 2023-06-14 17:05 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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 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
> www.mokadb.com
>





^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2023-06-14 17:27  David G. Johnston <david.g.johnston@gmail.com>
  parent: Marc Millas <marc.millas@mokadb.com>
  1 sibling, 1 reply; 31+ messages in thread

From: David G. Johnston @ 2023-06-14 17:27 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2023-06-14 18:02  Marc Millas <marc.millas@mokadb.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 2 replies; 31+ messages in thread

From: Marc Millas @ 2023-06-14 18:02 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2023-06-14 18:14  Ron <ronljohnsonjr@gmail.com>
  parent: Marc Millas <marc.millas@mokadb.com>
  1 sibling, 1 reply; 31+ messages in thread

From: Ron @ 2023-06-14 18:14 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2023-06-14 18:23  Marc Millas <marc.millas@mokadb.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 2 replies; 31+ messages in thread

From: Marc Millas @ 2023-06-14 18:23 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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.
>

^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2023-06-14 18:24  Marc Millas <marc.millas@mokadb.com>
  parent: Marc Millas <marc.millas@mokadb.com>
  1 sibling, 0 replies; 31+ messages in thread

From: Marc Millas @ 2023-06-14 18:24 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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.
>>
>

^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2023-06-14 18:53  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Marc Millas <marc.millas@mokadb.com>
  1 sibling, 0 replies; 31+ messages in thread

From: Adrian Klaver @ 2023-06-14 18:53 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: date format
@ 2023-06-14 20:44  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Marc Millas <marc.millas@mokadb.com>
  1 sibling, 0 replies; 31+ messages in thread

From: Laurenz Albe @ 2023-06-14 20:44 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 31+ messages in thread


end of thread, other threads:[~2023-06-14 20:44 UTC | newest]

Thread overview: 31+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-12-11 16:38 Date format Constantine Manoussakis <cmanoussakis@yahoo.com>
2000-01-14 16:19 Date format Patrick Welche <prlw1@newn.cam.ac.uk>
2003-04-02 22:59 date format scott.marlowe <scott.marlowe@ihs.com>
2003-04-03 05:57 ` Re: date format Tony Grant <tony@tgds.net>
2005-12-08 17:44 date format David Rysdam <drysdam@ll.mit.edu>
2005-12-08 18:02 ` Re: date format David Rysdam <drysdam@ll.mit.edu>
2005-12-08 18:12   ` Re: date format David Rysdam <drysdam@ll.mit.edu>
2007-03-04 12:47 date format Garry Saddington <garry@schoolteachers.co.uk>
2007-03-04 12:58 ` Re: date format Jorge Godoy <jgodoy@gmail.com>
2007-03-04 15:28   ` Re: date format Raymond O'Donnell <rod@iol.ie>
2007-03-04 16:16     ` Re: date format Garry Saddington <garry@schoolteachers.co.uk>
2007-03-04 16:16       ` Re: date format Raymond O'Donnell <rod@iol.ie>
2007-03-04 17:05         ` Re: date format Garry Saddington <garry@schoolteachers.co.uk>
2007-03-04 17:09           ` Re: date format Martijn van Oosterhout <kleptog@svana.org>
2007-03-04 18:56             ` Re: date format Garry Saddington <garry@schoolteachers.co.uk>
2007-03-04 19:26               ` Re: date format Raymond O'Donnell <rod@iol.ie>
2007-03-04 19:34                 ` Re: date format Raymond O'Donnell <rod@iol.ie>
2007-03-05 10:04         ` Re: date format Peter Eisentraut <peter_e@gmx.net>
2007-03-05 12:16           ` Re: date format Joshua D. Drake <jd@commandprompt.com>
2007-03-05 12:29             ` Re: date format Raymond O'Donnell <rod@iol.ie>
2007-03-04 18:32 ` Re: date format Joshua D. Drake <jd@commandprompt.com>
2007-03-05 06:14 ` Re: date format Tino Wildenhain <tino@wildenhain.de>
2023-06-14 16:42 date format Marc Millas <marc.millas@mokadb.com>
2023-06-14 17:05 ` Re: date format Michael Nolan <htfoot@gmail.com>
2023-06-14 17:27 ` Re: date format David G. Johnston <david.g.johnston@gmail.com>
2023-06-14 18:02   ` Re: date format Marc Millas <marc.millas@mokadb.com>
2023-06-14 18:14     ` Re: date format Ron <ronljohnsonjr@gmail.com>
2023-06-14 18:23       ` Re: date format Marc Millas <marc.millas@mokadb.com>
2023-06-14 18:24         ` Re: date format Marc Millas <marc.millas@mokadb.com>
2023-06-14 18:53         ` Re: date format Adrian Klaver <adrian.klaver@aklaver.com>
2023-06-14 20:44     ` Re: date format Laurenz Albe <laurenz.albe@cybertec.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox