Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.

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:

On Wed, Sep 10, 2003 at 12:52:22PM +0200, Torello Querci wrote:

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?

What's happen if you try:

test=# select '03/06/1973'::date;
date
------------
1973-06-03

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

On 7.3.2 and 7.3.4 obtain the same result:

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

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

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

template1=# 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=# select to_timestamp('1973/06/03','yyyy/mm/dd');
to_timestamp
------------------------
1973-06-02 23:00:00+01
(1 row)

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

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

Alle 15:33, mercoledì 10 settembre 2003, hai scritto:

Am Mittwoch, 10. September 2003 12:52 schrieb Torello Querci:

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?

Could be Mandrake or compiler problem.
# select version();
version
---------------------------------------------------------------
PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

# select to_date('03/06/1973','dd/mm/yyyy');
to_date
------------
1973-06-03
(1 row)

This is on Debian woody with backported postgres from testing.

HTH,
Daniel
--
Daniel Schreiber | ICQ: 220903493
GPG encrypted Mail welcome! Key ID: 25A6B489
Chemnitzer Linux-Tag:
http://www.tu-chemnitz.de/linux/tag/

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@zf.jcu.cz> wrote:

On Wed, Sep 10, 2003 at 12:52:22PM +0200, Torello
Querci wrote:

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?

What's happen if you try:

test=# select '03/06/1973'::date;
date
------------
1973-06-03

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

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

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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:

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@zf.jcu.cz> wrote:

On Wed, Sep 10, 2003 at 12:52:22PM +0200, Torello
Querci wrote:

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?

What's happen if you try:

test=# select '03/06/1973'::date;
date
------------
1973-06-03

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

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

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

--
Ing. Torello Querci
Responsabile Architetture Software
Negens S.r.l.

Tel. +39-055-5352846/7
e-mail: querci@negens.com

Torello Querci <querci@negens.com> writes:

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

What timezone are you in? What do you get from
select to_timestamp('03/06/1973','dd/mm/yyyy');

regards, tom lane

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:

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

--
Jord Tanner <jord@indygecko.com>

Jord Tanner <jord@indygecko.com> writes:

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

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

I thought I would try to make 7.4beta2 on my os X
machine. I used the default ./configure then make.
make says everything is ok to install so I tried a
make check.. this is my error log.

gcc is set for 3.1
os X 10.2.6.

I don't know if this is just a 'make check' problem or
if what I have is NFG.

Ted

Running in noclean mode. Mistakes will not be cleaned
up.
The files belonging to this database system will be
owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale
C.

creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data...
ok
creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/base...
ok
creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/global...
ok
creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/pg_xlog...
ok
creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/pg_clog...
ok
selecting default shared_buffers... 200
selecting default max_connections... 30
creating configuration files... ok
creating template1 database in
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/base/1...
FATAL: could not create shared memory segment:
Invalid argument
DETAIL: Failed syscall was shmget(key=1,
size=10444800, 03600).
HINT: This error usually means that PostgreSQL's
request for a shared memory segment exceeded your
kernel's SHMMAX parameter. You can either reduce the
request size or reconfigure the kernel with larger
SHMMAX. To reduce the request size (currently 10444800
bytes), reduce PostgreSQL's shared_buffers parameter
(currently 1000) and/or its max_connections parameter
(currently 100).
If the request size is already small, it's possible
that it is less than your kernel's SHMMIN parameter,
in which case raising the request size or
reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information
about shared memory configuration.

initdb: failed
initdb: data directory
"/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data"
not removed at user's request

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:

I is not a bug.

Yes it is; it's fixed in beta3.

Its just your OSX is too conservative regarding
IPC defaults.

PG knows that, but we made a last-minute change in beta2 that caused the
later part of the initdb process to forget to use the numbers that the
earlier part determined. Please check that beta3 works *without* upping
your shmmax.

regards, tom lane

I is not a bug.

Its just your OSX is too conservative regarding
IPC defaults.

Consider changing these variables:

kern.ipc.shmmax
kern.ipc.shm_use_phys (for performance only)
kern.ipc.shmall

They apply to FreeBSD, but OSX must have something similar.

On Tue, 16 Sep 2003, Theodore Petrosky wrote:

I thought I would try to make 7.4beta2 on my os X
machine. I used the default ./configure then make.
make says everything is ok to install so I tried a
make check.. this is my error log.

gcc is set for 3.1
os X 10.2.6.

I don't know if this is just a 'make check' problem or
if what I have is NFG.

Ted

Running in noclean mode. Mistakes will not be cleaned
up.
The files belonging to this database system will be
owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale
C.

creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data...
ok
creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/base...
ok
creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/global...
ok
creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/pg_xlog...
ok
creating directory
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/pg_clog...
ok
selecting default shared_buffers... 200
selecting default max_connections... 30
creating configuration files... ok
creating template1 database in
/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data/base/1...
FATAL: could not create shared memory segment:
Invalid argument
DETAIL: Failed syscall was shmget(key=1,
size=10444800, 03600).
HINT: This error usually means that PostgreSQL's
request for a shared memory segment exceeded your
kernel's SHMMAX parameter. You can either reduce the
request size or reconfigure the kernel with larger
SHMMAX. To reduce the request size (currently 10444800
bytes), reduce PostgreSQL's shared_buffers parameter
(currently 1000) and/or its max_connections parameter
(currently 100).
If the request size is already small, it's possible
that it is less than your kernel's SHMMIN parameter,
in which case raising the request size or
reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information
about shared memory configuration.

initdb: failed
initdb: data directory
"/Users/postgres/postgres/postgresql-7.4beta2/src/test/regress/./tmp_check/data"
not removed at user's request

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill at matrix dot gatewaynet dot com
mantzios at softlab dot ece dot ntua dot gr