[SQL] timestamp bug
There appears to be a bug in timestamp/interval addition. It happens in
both PG version 7.0.2 and 7.1. There is a duplicate day (2001 Sep 07) and
a missing day (2002 Apr 04). I discovered this by accident when I asked
the interface I'm writing for a 365 day long calendar.. Interestingly,
the missing day thing (second example) doesn't happen if only adding a few
days (like the first example). I didn't go into detail to find the point
at which it does happen.
-Cedar
devbarn71=# SELECT version();
version
---
PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('1 days' as
interval),' Mon DD');
to_char
-
2001 Sep 06
(1 row)
devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('2 days' as
interval),' Mon DD');
to_char
-
2001 Sep 07
(1 row)
devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('3 days' as
interval),' Mon DD');
to_char
-
2001 Sep 07
(1 row)
devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('4 days' as
interval),' Mon DD');
to_char
-
2001 Sep 08
(1 row)
devbarn71=# SELECT to_char(cast('2001 apr 8' as timestamp)+cast('361 days' as
interval),' Mon DD');
to_char
-
2002 Apr 03
(1 row)
devbarn71=# SELECT to_char(cast('2001 apr 8' as timestamp)+cast('362 days' as
interval),' Mon DD');
to_char
-
2002 Apr 04
(1 row)
devbarn71=# SELECT to_char(cast('2001 apr 8' as timestamp)+cast('363 days' as
interval),' Mon DD');
to_char
-
2002 Apr 06
(1 row)
devbarn71=# SELECT to_char(cast('2001 apr 8' as timestamp)+cast('364 days' as
interval),' Mon DD');
to_char
-
2002 Apr 07
(1 row)
As an afterthought,
cedarc@nanu:~/schedule/fe$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 7
model name : Pentium III (Katmai)
stepping: 3
cpu MHz : 498.379505
cache size : 512 KB
fdiv_bug: no
hlt_bug : no
sep_bug : no
f00f_bug: no
coma_bug: no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 mmx osfxsr kni
bogomips: 496.44
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Calculating the age of a person
Or you could use date_trunc() in the same way as date_part() if you want
an interval instead..
SELECT date_trunc('year',age(birth)),* FROM persons LIMIT 1;
On Fri, 18 May 2001, [EMAIL PROTECTED] wrote:
> You are probably looking for date_part().
>
> E.g.
>
> SELECT date_part('year',age(birth)),* FROM persons LIMIT 1;
>
>
> Troy
>
>
>
> >
> > I have a table containing the birthdays of various persons. The target
> > is to compute the age of a persons.
> >
> > persons=# SELECT age(birth), * FROM persons LIMIT 1;
> > age | id | name | birth| gender |
> > income
> > ---+++++
> >
> > 31 years 4 mons 16 days 23:00 | 1 | Albert | 1970-01-01 | m |
> > 35000
> > (1 row)
> >
> > When I use age() I don't get full years. Is there an easy way to round
> > ::reltime off or up without writing a function. Is there any possibility
> > to use plain SQL only?
> >
> > Hans
> >
> >
> > ---(end of broadcast)---
> > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> >
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] problem while starting server ??? (fwd)
I don't know how this got to me when it was sent to [EMAIL PROTECTED], but anyway here's the answer: psql -U postgres template1 On Sun, 20 May 2001, Subhramanya Shiva wrote: > Hi all > > I installed postgresql successfully. > But when i starting the server it is showing the server status differently > > DEBUG: CheckPoint record at (0, 1522288) > DEBUG: Redo record at (0, 1522288); Undo record at (0, 0); Shutdown TRUE > DEBUG: NextTransactionId: 618; NextOid: 18720 > DEBUG: database system is in production state > > after this > when i was starting the psql > it was giving error "user not found in the system catalog" > > what could be the problem Installation i did it correctly. > after gmake and gmake install ..it showed ...Postregsql > Installed successfully. > > regards > shiva. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
