pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcasting interval to time
8+ messages / 3 participants
[nested] [flat]
* casting interval to time
@ 2003-06-16 13:51 Tomasz Myrta <jasiek@klaster.net>
2003-06-16 14:53 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-06-16 13:51 UTC (permalink / raw)
To: pgsql-sql
Hi
psql -V
psql (PostgreSQL) 7.3
SELECT cast(cast('1 day 12 hours' as interval) as time);
time
----------
12:00:00
(1 row)
psql -V
psql (PostgreSQL) 7.3.2
SELECT cast(cast('1 day 12 hours' as interval) as time);
time
----------
00:00:00
(1 row)
Did I miss something? I looked into 'history' file, but I couldn't find
anything interesting about changes in casting interval into time.
I found replacement for this problem, but I wan't just to know what
happened:
('1970-1-1'::date+some_interval)::time
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: casting interval to time
2003-06-16 13:51 casting interval to time Tomasz Myrta <jasiek@klaster.net>
@ 2003-06-16 14:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:00 ` Re: casting interval to time Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-06-16 14:53 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
Tomasz Myrta <jasiek@klaster.net> writes:
> psql (PostgreSQL) 7.3.2
> SELECT cast(cast('1 day 12 hours' as interval) as time);
> time
> ----------
> 00:00:00
> (1 row)
I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: casting interval to time
2003-06-16 13:51 casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 14:53 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-06-16 15:00 ` Tomasz Myrta <jasiek@klaster.net>
2003-06-16 15:17 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-06-16 15:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Dnia 2003-06-16 16:53, Użytkownik Tom Lane napisał:
> Tomasz Myrta <jasiek@klaster.net> writes:
>
>>psql (PostgreSQL) 7.3.2
>>SELECT cast(cast('1 day 12 hours' as interval) as time);
>> time
>>----------
>> 00:00:00
>>(1 row)
>
>
> I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
Default Debian package. I got the same result even on a 7.3.3 Debian
package.
postgresql.conf contains:
DATESTYLE = 'iso,european'
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: casting interval to time
2003-06-16 13:51 casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 14:53 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:00 ` Re: casting interval to time Tomasz Myrta <jasiek@klaster.net>
@ 2003-06-16 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:56 ` Re: casting interval to time Antti Haapala <antti.haapala@iki.fi>
2003-06-16 16:00 ` Re: casting interval to time Tomasz Myrta <jasiek@klaster.net>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2003-06-16 15:17 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
Tomasz Myrta <jasiek@klaster.net> writes:
> Dnia 2003-06-16 16:53, U¿ytkownik Tom Lane napisa³:
>> Tomasz Myrta <jasiek@klaster.net> writes:
>>
>>> psql (PostgreSQL) 7.3.2
>>> SELECT cast(cast('1 day 12 hours' as interval) as time);
>>> time
>>> ----------
>>> 00:00:00
>>> (1 row)
>>
>>
>> I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
> Default Debian package. I got the same result even on a 7.3.3 Debian
> package.
What do you get from "pg_config --configure"? What's the hardware
platform --- i386, or something else? Can anyone else reproduce this,
on any platform?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: casting interval to time
2003-06-16 13:51 casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 14:53 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:00 ` Re: casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 15:17 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-06-16 15:56 ` Antti Haapala <antti.haapala@iki.fi>
2003-06-16 17:51 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 19:02 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 8+ messages in thread
From: Antti Haapala @ 2003-06-16 15:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
On Mon, 16 Jun 2003, Tom Lane wrote:
> Tomasz Myrta <jasiek@klaster.net> writes:
> > Dnia 2003-06-16 16:53, U¿ytkownik Tom Lane napisa³:
> > > Tomasz Myrta <jasiek@klaster.net> writes:
> > >
> > > > psql (PostgreSQL) 7.3.2
> > > > SELECT cast(cast('1 day 12 hours' as interval) as time);
> > > > time
> > > > ----------
> > > > 00:00:00
> > > > (1 row)
> > >
> > >
> > > I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
>
> > Default Debian package. I got the same result even on a 7.3.3 Debian
> > package.
>
> What do you get from "pg_config --configure"? What's the hardware
> platform --- i386, or something else? Can anyone else reproduce this,
> on any platform?
template1=# select cast('25 hours'::interval as time);
time
----------
00:00:00
(1 row)
template1=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)
It's also Debian (3.0).
% pg_config --configure '--host=i386-linux' '--build=i386-linux'
'--prefix=/usr' '--mandir=/usr/share/man' '--docdir=/usr/share/doc'
'--bindir=/usr/lib/postgresql/bin' '--libdir=/usr/lib'
'--includedir=/usr/include/postgresql' '--enable-recode' '--enable-nls'
'--enable-integer-datetimes' (could be this?)
'--enable-debug'
'--disable-rpath' '--with-tcl' '--with-perl' '--with-python' '--with-pam'
'--with-openssl' '--with-gnu-ld' '--with-krb5'
'--with-tclconfig=/usr/lib/tcl8.4' '--with-tkconfig=/usr/lib/tk8.4'
'--with-includes=/usr/include/tcl8.4' '--with-maxbackends=64'
'--with-pgport=5432'
'DOCBOOKSTYLE=/usr/share/sgml/docbook/stylesheet/dsssl/modular'
'build_alias=i386-linux' 'host_alias=i386-linux'
---
Not affected: GentooLinux 1.4 rc2 x86 w/ pg 7.3, gcc 3.2.1, glibc 2.3.1 w/
following configure options:
'--prefix=/usr' '--mandir=/usr/share/man' '--host=i586-pc-linux-gnu'
'--docdir=/usr/share/doc/postgresql-7.3' '--libdir=/usr/lib'
'--enable-syslog' '--enable-depend' '--with-gnu-ld' '--with-pam'
'--with-maxbackends=1024' '--with-python' '--with-perl' '--with-java'
'--with-openssl' '--enable-locale' '--enable-nls' '--enable-multibyte'
'--with-CXX' 'CC=gcc' 'CFLAGS=-march=pentium-mmx -O3 -pipe'
'host_alias=i586-pc-linux-gnu'
--
Antti Haapala
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: casting interval to time
2003-06-16 13:51 casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 14:53 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:00 ` Re: casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 15:17 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:56 ` Re: casting interval to time Antti Haapala <antti.haapala@iki.fi>
@ 2003-06-16 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2003-06-16 17:51 UTC (permalink / raw)
To: Antti Haapala <antti.haapala@iki.fi>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
Antti Haapala <antti.haapala@iki.fi> writes:
> '--enable-integer-datetimes' (could be this?)
Bingo. I can reproduce it with that configure choice.
Should have the answer soon ...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: casting interval to time
2003-06-16 13:51 casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 14:53 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:00 ` Re: casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 15:17 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:56 ` Re: casting interval to time Antti Haapala <antti.haapala@iki.fi>
@ 2003-06-16 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2003-06-16 19:02 UTC (permalink / raw)
To: Antti Haapala <antti.haapala@iki.fi>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
Antti Haapala <antti.haapala@iki.fi> writes:
> It's also Debian (3.0).
On investigation the interval_time() function was completely broken for
the --enable-integer-datetimes case --- it was reducing the interval
value modulo one second, rather than modulo one day as intended.
I also noticed that neither the integer nor float case behaved
rationally for negative intervals. I've applied the attached patch
to 7.3 and HEAD ...
regards, tom lane
inline patch 1: src/backend/utils/adt/date.c (+19/-6 lines) download inline-1.patch
*** src/backend/utils/adt/date.c.orig Thu Feb 13 12:04:24 2003
--- src/backend/utils/adt/date.c Mon Jun 16 14:56:53 2003
***************
*** 999,1004 ****
--- 999,1009 ----
/* interval_time()
* Convert interval to time data type.
+ *
+ * This is defined as producing the fractional-day portion of the interval.
+ * Therefore, we can just ignore the months field. It is not real clear
+ * what to do with negative intervals, but we choose to subtract the floor,
+ * so that, say, '-2 hours' becomes '22:00:00'.
*/
Datum
interval_time(PG_FUNCTION_ARGS)
***************
*** 1007,1021 ****
TimeADT result;
#ifdef HAVE_INT64_TIMESTAMP
result = span->time;
! if ((result >= INT64CONST(86400000000))
! || (result <= INT64CONST(-86400000000)))
! result -= (result / INT64CONST(1000000) * INT64CONST(1000000));
#else
- Interval span1;
-
result = span->time;
! TMODULO(result, span1.time, 86400e0);
#endif
PG_RETURN_TIMEADT(result);
--- 1012,1034 ----
TimeADT result;
#ifdef HAVE_INT64_TIMESTAMP
+ int64 days;
+
result = span->time;
! if (result >= INT64CONST(86400000000))
! {
! days = result / INT64CONST(86400000000);
! result -= days * INT64CONST(86400000000);
! }
! else if (result < 0)
! {
! days = (-result + INT64CONST(86400000000-1)) / INT64CONST(86400000000);
! result += days * INT64CONST(86400000000);
! }
#else
result = span->time;
! if (result >= 86400e0 || result < 0)
! result -= floor(result / 86400e0) * 86400e0;
#endif
PG_RETURN_TIMEADT(result);
* Re: casting interval to time
2003-06-16 13:51 casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 14:53 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:00 ` Re: casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 15:17 ` Re: casting interval to time Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-06-16 16:00 ` Tomasz Myrta <jasiek@klaster.net>
1 sibling, 0 replies; 8+ messages in thread
From: Tomasz Myrta @ 2003-06-16 16:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Dnia 2003-06-16 17:17, Użytkownik Tom Lane napisał:
> What do you get from "pg_config --configure"? What's the hardware
> platform --- i386, or something else? Can anyone else reproduce this,
> on any platform?
The platform is i386.
There was no "pg_config" file in binary package. After copying this file
from source tgz, I get only one line result:
@configure@
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-06-16 19:02 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-16 13:51 casting interval to time Tomasz Myrta <jasiek@klaster.net>
2003-06-16 14:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:00 ` Tomasz Myrta <jasiek@klaster.net>
2003-06-16 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 15:56 ` Antti Haapala <antti.haapala@iki.fi>
2003-06-16 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 16:00 ` Tomasz Myrta <jasiek@klaster.net>
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