pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDate 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>
2003-04-03 05:57 ` Re: date format Tony Grant <tony@tgds.net>
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-02 22:59 date format scott.marlowe <scott.marlowe@ihs.com>
@ 2003-04-03 05:57 ` Tony Grant <tony@tgds.net>
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>
2005-12-08 18:02 ` Re: date format 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 17:44 date format David Rysdam <drysdam@ll.mit.edu>
@ 2005-12-08 18:02 ` David Rysdam <drysdam@ll.mit.edu>
2005-12-08 18:12 ` Re: date format 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 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 ` 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>
2007-03-04 12:58 ` Re: date format Jorge Godoy <jgodoy@gmail.com>
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>
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:47 date format Garry Saddington <garry@schoolteachers.co.uk>
@ 2007-03-04 12:58 ` Jorge Godoy <jgodoy@gmail.com>
2007-03-04 15:28 ` Re: date format Raymond O'Donnell <rod@iol.ie>
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 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 ` Raymond O'Donnell <rod@iol.ie>
2007-03-04 16:16 ` Re: date format Garry Saddington <garry@schoolteachers.co.uk>
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 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 ` Garry Saddington <garry@schoolteachers.co.uk>
2007-03-04 16:16 ` Re: date format 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 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 ` Raymond O'Donnell <rod@iol.ie>
2007-03-04 17:05 ` Re: date format Garry Saddington <garry@schoolteachers.co.uk>
2007-03-05 10:04 ` Re: date format Peter Eisentraut <peter_e@gmx.net>
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 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 ` Garry Saddington <garry@schoolteachers.co.uk>
2007-03-04 17:09 ` Re: date format Martijn van Oosterhout <kleptog@svana.org>
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 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 ` Martijn van Oosterhout <kleptog@svana.org>
2007-03-04 18:56 ` Re: date format 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 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 ` Garry Saddington <garry@schoolteachers.co.uk>
2007-03-04 19:26 ` Re: date format Raymond O'Donnell <rod@iol.ie>
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 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 ` Raymond O'Donnell <rod@iol.ie>
2007-03-04 19:34 ` Re: date format Raymond O'Donnell <rod@iol.ie>
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 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 ` 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-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-05 10:04 ` Peter Eisentraut <peter_e@gmx.net>
2007-03-05 12:16 ` Re: date format Joshua D. Drake <jd@commandprompt.com>
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-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-05 10:04 ` Re: date format Peter Eisentraut <peter_e@gmx.net>
@ 2007-03-05 12:16 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-05 12:29 ` Re: date format Raymond O'Donnell <rod@iol.ie>
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-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-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 ` Raymond O'Donnell <rod@iol.ie>
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
* Re: date format
2007-03-04 12:47 date format Garry Saddington <garry@schoolteachers.co.uk>
@ 2007-03-04 18:32 ` Joshua D. Drake <jd@commandprompt.com>
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 12:47 date format Garry Saddington <garry@schoolteachers.co.uk>
@ 2007-03-05 06:14 ` Tino Wildenhain <tino@wildenhain.de>
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
* date format
@ 2023-06-14 16:42 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>
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 16:42 date format Marc Millas <marc.millas@mokadb.com>
@ 2023-06-14 17:05 ` Michael Nolan <htfoot@gmail.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 16:42 date format Marc Millas <marc.millas@mokadb.com>
@ 2023-06-14 17:27 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-14 18:02 ` Re: date format 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 16:42 date format Marc Millas <marc.millas@mokadb.com>
2023-06-14 17:27 ` Re: date format David G. Johnston <david.g.johnston@gmail.com>
@ 2023-06-14 18:02 ` Marc Millas <marc.millas@mokadb.com>
2023-06-14 18:14 ` Re: date format Ron <ronljohnsonjr@gmail.com>
2023-06-14 20:44 ` Re: date format Laurenz Albe <laurenz.albe@cybertec.at>
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 16:42 date format Marc Millas <marc.millas@mokadb.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 ` Ron <ronljohnsonjr@gmail.com>
2023-06-14 18:23 ` Re: date format 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 16:42 date format Marc Millas <marc.millas@mokadb.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 ` 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>
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 16:42 date format Marc Millas <marc.millas@mokadb.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 ` 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 16:42 date format Marc Millas <marc.millas@mokadb.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:53 ` Adrian Klaver <adrian.klaver@aklaver.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 16:42 date format Marc Millas <marc.millas@mokadb.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 20:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
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