Thread: Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.
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?
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:
> 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 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/
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/
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
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
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
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:
> 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 withMandra=
ke
> > 9.1 and Mandrake 9.2RC1 and obtain the same result.
> >
> > Can anyone help me?
>
> What's happen if you try:
>
> test=3D# select '03/06/1973'::date;
> date
> ------------
> 1973-06-03
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
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
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
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