Thread: BUG #1927: incorrect timestamp returned
The following bug has been logged online: Bug reference: 1927 Logged by: Deyan Chepishev Email address: updates@blue-edge.bg PostgreSQL version: 7.4.8, 8.0.3 Operating system: Linux Description: incorrect timestamp returned Details: Postgre: 1. PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 20050227 (Red Hat 3.4.3-22.1) 2. PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 When I run the following query: select timestamp '2005-09-23 23:59:59.9999999' I get the following result (note the value of the seconds): 2005-09-23 23:59:60.00
Deyan Chepishev wrote: > The following bug has been logged online: > > Bug reference: 1927 > Logged by: Deyan Chepishev > Email address: updates@blue-edge.bg > PostgreSQL version: 7.4.8, 8.0.3 > Operating system: Linux > Description: incorrect timestamp returned > Details: > > Postgre: > 1. PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 > 20050227 (Red Hat 3.4.3-22.1) > 2. PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 > > When I run the following query: > > select timestamp '2005-09-23 23:59:59.9999999' > > I get the following result (note the value of the seconds): > > 2005-09-23 23:59:60.00 It's the leap second. Regards Gaetano Mendola
>> >>select timestamp '2005-09-23 23:59:59.9999999' >> >>I get the following result (note the value of the seconds): >> >>2005-09-23 23:59:60.00 > > > It's the leap second. > > It is not a leap second. Leap seconds are always either on the 30th of June or on the 31th of December. Here there is a list of all the leap seconds so far: http://en.wikipedia.org/wiki/Leap_second And, in fact, the wrong result is the same for each date, regardless of the year, month or day. -- Kouber Saparev http://kouber.saparev.com/
Kouber Saparev wrote: > >> > >>select timestamp '2005-09-23 23:59:59.9999999' > >> > >>I get the following result (note the value of the seconds): > >> > >>2005-09-23 23:59:60.00 > > > > > > It's the leap second. > > > > > > It is not a leap second. Leap seconds are always either on the 30th of > June or on the 31th of December. > > Here there is a list of all the leap seconds so far: > http://en.wikipedia.org/wiki/Leap_second > > And, in fact, the wrong result is the same for each date, regardless of > the year, month or day. Right. We allow leap seconds for any date/time. Are you saying we should only allow them for certain dates/times? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Alvaro Herrera wrote: > On Tue, Oct 04, 2005 at 10:40:11AM -0400, Bruce Momjian wrote: > > Kouber Saparev wrote: > > > >> > > > >>select timestamp '2005-09-23 23:59:59.9999999' > > > >> > > > >>I get the following result (note the value of the seconds): > > > >> > > > >>2005-09-23 23:59:60.00 > > > > > > > > It's the leap second. > > > > > > It is not a leap second. Leap seconds are always either on the 30th of > > > June or on the 31th of December. > > > > > > Here there is a list of all the leap seconds so far: > > > http://en.wikipedia.org/wiki/Leap_second > > > > > > And, in fact, the wrong result is the same for each date, regardless of > > > the year, month or day. > > > > Right. We allow leap seconds for any date/time. Are you saying we > > should only allow them for certain dates/times? > > We should do that for timestamp or timestamptz, but there's no way we > could check for a bare time or timetz ... > > Also it'd require a recompile whenever a new leap second is added; and > that the Wikipedia hints that leap seconds may disappear in 2008 in > favor of "leap hours", whatever that may be. Yea, I am thinking our current behavior is as good as we can make it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, Oct 04, 2005 at 10:40:11AM -0400, Bruce Momjian wrote: > Kouber Saparev wrote: > > >> > > >>select timestamp '2005-09-23 23:59:59.9999999' > > >> > > >>I get the following result (note the value of the seconds): > > >> > > >>2005-09-23 23:59:60.00 > > > > > > It's the leap second. > > > > It is not a leap second. Leap seconds are always either on the 30th of > > June or on the 31th of December. > > > > Here there is a list of all the leap seconds so far: > > http://en.wikipedia.org/wiki/Leap_second > > > > And, in fact, the wrong result is the same for each date, regardless of > > the year, month or day. > > Right. We allow leap seconds for any date/time. Are you saying we > should only allow them for certain dates/times? We should do that for timestamp or timestamptz, but there's no way we could check for a bare time or timetz ... Also it'd require a recompile whenever a new leap second is added; and that the Wikipedia hints that leap seconds may disappear in 2008 in favor of "leap hours", whatever that may be. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "That sort of implies that there are Emacs keystrokes which aren't obscure. I've been using it daily for 2 years now and have yet to discover any key sequence which makes any sense." (Paul Thomas)
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Right. We allow leap seconds for any date/time. Are you saying we > > should only allow them for certain dates/times? > > No, his point is the funny roundoff behavior. > > regression=# select timestamp '2005-09-23 23:59:59.999999'; > timestamp > ---------------------------- > 2005-09-23 23:59:59.999999 > (1 row) > > regression=# select timestamp '2005-09-23 23:59:59.9999999'; > timestamp > ------------------------ > 2005-09-23 23:59:60.00 > (1 row) > > regression=# select timestamp '2005-09-23 23:59:59.99999999'; > timestamp > --------------------- > 2005-09-24 00:00:00 > (1 row) Wow, that is funny roundoff behavior. Has anyone researched the cause? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Right. We allow leap seconds for any date/time. Are you saying we
> should only allow them for certain dates/times?
No, his point is the funny roundoff behavior.
regression=# select timestamp '2005-09-23 23:59:59.999999';
timestamp
----------------------------
2005-09-23 23:59:59.999999
(1 row)
regression=# select timestamp '2005-09-23 23:59:59.9999999';
timestamp
------------------------
2005-09-23 23:59:60.00
(1 row)
regression=# select timestamp '2005-09-23 23:59:59.99999999';
timestamp
---------------------
2005-09-24 00:00:00
(1 row)
regards, tom lane
On Tue, Oct 04, 2005 at 11:20:15AM -0400, Alvaro Herrera wrote: > On Tue, Oct 04, 2005 at 10:40:11AM -0400, Bruce Momjian wrote: > > Kouber Saparev wrote: > > > >> > > > >>select timestamp '2005-09-23 23:59:59.9999999' > > > >> > > > >>I get the following result (note the value of the seconds): > > > >> > > > >>2005-09-23 23:59:60.00 > > > > > > > > It's the leap second. > > > > > > It is not a leap second. Leap seconds are always either on the 30th of > > > June or on the 31th of December. > > > > > > Here there is a list of all the leap seconds so far: > > > http://en.wikipedia.org/wiki/Leap_second > > > > > > And, in fact, the wrong result is the same for each date, regardless of > > > the year, month or day. > > > > Right. We allow leap seconds for any date/time. Are you saying we > > should only allow them for certain dates/times? > > We should do that for timestamp or timestamptz, but there's no way we > could check for a bare time or timetz ... > > Also it'd require a recompile whenever a new leap second is added; and > that the Wikipedia hints that leap seconds may disappear in 2008 in > favor of "leap hours", whatever that may be. Well, if they only add leap seconds on pre-defined days then we could only allow them on those days. To wit: "Leap seconds occur only at the end of a UTC month, and have only ever been inserted at the end of June 30 or December 31. Unlike leap days, they occur simultaneously worldwide; for example, a leap second on 31 December will be observed as 6:59:60 pm U.S. Eastern Standard Time." So that means that the only time a leap second should be allowed is June 30 23:59:60 *UTC* or Dec. 31 23:59:60 *UTC*. Given the recent discussion about time rounding issues, etc, it might actually make sense to do this. Something else to note: "The announcement to insert a leap second is usually issued whenever the difference between UTC and UT1 approaches 0.7s, to keep the difference between UTC and UT1 from exceeding ±0.9 s. After UTC 23:59:59, a positive leap second at 23:59:60 would be counted, before the clock indicates 00:00:00 of the next day. Negative leap seconds are also possible should the Earth's rotation become slightly faster; in that case, 23:59:58 would be followed by 00:00:00." Obviously, trying to comply with that to the letter would be a heck of a lot harder. Unfortunately, if we ever do have a negative leap second I suspect it could confuse a whole lot of applications... I wonder if there are any users who actaully care about these differences. I suspect astronomers have some other time system they use... can't think of anyone else who might care. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461