Обсуждение: casting interval to time
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
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
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
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
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
byGCC 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
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
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
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
*** 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);