Hi to all,

I have a problem storing 1973/06/03 date.

If I send this statement

select to_date('03/06/1973','dd/mm/yyyy');

in the psql interface I obtain

to_date
------------
1973-06-02

I test this statement with Postgres 7.3.2 and 7.3.4 packaged withMandrake 9.1
and Mandrake 9.2RC1 and obtain the same result.

Can anyone help me?

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On my 7.4 this select works fine

testdb011=> select to_date('03/06/1973','dd/mm/yyyy');
to_date
------------
1973-06-03

Pavel


On Wed, 10 Sep 2003, Torello Querci wrote:


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

I think this is what you wanted.

select to_char('03/06/1973'::date,'dd/mm/yyyy');

you were casting the string to a date and returning a
date. so it would return it in a format that your
system is set to return.

agencysacks=# select
to_char('03/06/1973'::date,'dd/mm/yyyy');
to_char
------------
06/03/1973
(1 row)



Ted

--- Karel Zak <zakkr (AT) zf (DOT) jcu.cz> wrote:
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

This is not a bug, but simply due to postgres date formatting. Observe
thus:


gbi=# set datestyle to 'iso';
SET
gbi=# select to_date('03/06/1973','dd/mm/yyyy');
to_date
------------
1973-06-03
(1 row)

gbi=# set datestyle to 'us';
SET
gbi=# select to_date('03/06/1973','dd/mm/yyyy');
to_date
------------
1973-06-03
(1 row)

gbi=# set datestyle to 'sql';
SET
gbi=# select to_date('03/06/1973','dd/mm/yyyy');
to_date
------------
06/03/1973
(1 row)


datestyle can be permanently set in postgresql.conf, or by the
environment variable 'PG_DATESTYLE';

On Wed, 2003-09-10 at 03:52, Torello Querci wrote:
Jord Tanner <jord (AT) indygecko (DOT) com>


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Jord Tanner <jord (AT) indygecko (DOT) com> writes:
Yes, it's a bug, but it's specific to the Europe/Rome timezone setting.
I've not yet had a chance to figure out the details.

regards, tom lane

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

My problem is related to the insert a date in a database having a date
as text. With your suggestion I use the database to convert a string to
another string.


I make another test using the function to_timestamp with this result:


gturn2=> select to_timestamp('1973/06/03','yyyy/mm/dd');
to_timestamp
------------------------
1973-06-02 23:00:00+01
(1 row)

gturn2=> select to_timestamp('1973/06/02','yyyy/mm/dd');
to_timestamp
------------------------
1973-06-02 00:00:00+01
(1 row)

gturn2=> select to_timestamp('1973/06/04','yyyy/mm/dd');
to_timestamp
------------------------
1973-06-04 00:00:00+02
(1 row)

I suppose that the problem is related to how the daylight (??) is
managed. Using this function is possible to use this workaround:


gturn2=> select (to_timestamp('1973/06/03','yyyy/mm/dd')+'6
hours'::interval)::date;
date
------------
1973-06-03
(1 row)

but is only a workaround.


Il mer, 2003-09-10 alle 17:34, Theodore Petrosky ha scritto:
Ing. Torello Querci
Responsabile Architetture Software
Negens S.r.l.

Tel. +39-055-5352846/7
e-mail: querci (AT) negens (DOT) com



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On 7.3.2 and 7.3.4 obtain the same result:

template1=3D# select '03/06/1973'::date;
date
------------
1973-03-06
(1 row)

template1=3D# select '02/06/1973'::date;
date
------------
1973-02-06
(1 row)

template1=3D# select '06/03/1973'::date;
date
------------
1973-06-03
(1 row)

template1=3D# select '06/02/1973'::date;
date
------------
1973-06-02
(1 row)




I suppose that the problem is related to the timezone. I make this test:



template1=3D# select to_timestamp('1973/06/03','yyyy/mm/dd');
to_timestamp
------------------------
1973-06-02 23:00:00+01
(1 row)

template1=3D# select to_timestamp('1973/06/02','yyyy/mm/dd');
to_timestamp
------------------------
1973-06-02 00:00:00+01
(1 row)

template1=3D# select to_timestamp('1973/06/04','yyyy/mm/dd');
to_timestamp
------------------------
1973-06-04 00:00:00+02


Alle 15:33, mercoled=EC 10 settembre 2003, hai scritto:
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org