Thread: Postgres storing time in strange manner
I don't have a clue why it's doing this - has anyone else seen this sort of
behavior, or know why it might be doing it? It shows times wrong, for
instance, it shows 00:04:60 where it should show 00:05:00. See below:
gabrielle=# select * from scheduled_query_groups;
group_id | group_name | group_type_id | interval | run_at |
last_run | exec_upon_completion
----------+------------+---------------+----------+-------------+------------
------------+---------------------- 2 | test | 1 | 00:04:60
| 00:00:00-05 | 0001-01-01 04:59:60.00 |
(1 row)
gabrielle=# \d scheduled_query_groups;
Table "scheduled_query_groups"
Column | Type |
Modifiers
----------------------+--------------------------+---------------------------
------------------------------- group_id | integer
| not null default
nextval('sq_groups_group_id_seq'::text)
group_name | character varying(32) | not null
group_type_id | integer | not null
interval | interval | not null default '5
minutes'
run_at | time with time zone | not null default '00:00:00
EST'
last_run | timestamp with time zone | not null default
'0001-01-01 00:00:00 EST'
exec_upon_completion | character varying(128) |
Primary key: scheduled_query_groups_pkey
Unique keys: scheduled_query__group_name_key
Triggers: RI_ConstraintTrigger_16607
--
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.
Lost Terminal.
On Sunday 15 September 2002 10:11 am, Rod Taylor wrote: > On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote: > > I don't have a clue why it's doing this - has anyone else seen this sort > > of behavior, or know why it might be doing it? It shows times wrong, for > > instance, it shows 00:04:60 where it should show 00:05:00. See below: > > There are actually 61 seconds in some minutes. In order to accommodate > leap seconds, PostgreSQL allows this to happen -- similarly to how it > will also allow 366 days in some years. How then, am I supposed to explain to a web interface user that when they just entered 5:00:00, it's going to sometimes show up to 4:59:60? I'm entering an exact timestamp, that being 5:00:00. Regardless of how many seconds you claim were in the former minute, it should not subtract a second from my entry, because 5:00:00 by your definition would mean 4:59 and 61 seconds. -- Casey Allen Shobe / Network Security Analyst & PHP Developer SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 cshobe@secureworks.net / http://www.secureworks.net Content is my own and does not necessarily represent my company. Dance like nobody's watching.
> On Sunday 15 September 2002 10:11 am, Rod Taylor wrote:
>> On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote:
>>> I don't have a clue why it's doing this - has anyone else seen this sort
>>> of behavior, or know why it might be doing it? It shows times wrong, for
>>> instance, it shows 00:04:60 where it should show 00:05:00. See below:
>>
>> There are actually 61 seconds in some minutes. In order to accommodate
>> leap seconds, PostgreSQL allows this to happen -- similarly to how it
>> will also allow 366 days in some years.
True but irrelevant -- PG does not do accounting for leap seconds.
The roundoff bug Casey is looking at is fixed in recent PG versions;
I'd recommend an update to 7.2.2.
regards, tom lane
On Sunday 15 September 2002 10:32 am, Tom Lane wrote: > The roundoff bug Casey is looking at is fixed in recent PG versions; > I'd recommend an update to 7.2.2. I'm running a freshly compiled version of 7.2.2 -- Casey Allen Shobe / Network Security Analyst & PHP Developer SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 cshobe@secureworks.net / http://www.secureworks.net Content is my own and does not necessarily represent my company. Dance like nobody's watching.
Casey Allen Shobe <cshobe@secureworks.net> writes:
> On Sunday 15 September 2002 10:32 am, Tom Lane wrote:
>> The roundoff bug Casey is looking at is fixed in recent PG versions;
>> I'd recommend an update to 7.2.2.
> I'm running a freshly compiled version of 7.2.2
Oh? On what platform?
regards, tom lane
Casey, > > There are actually 61 seconds in some minutes. In order to accommodate > > leap seconds, PostgreSQL allows this to happen -- similarly to how it > > will also allow 366 days in some years. > > How then, am I supposed to explain to a web interface user that when they just > entered 5:00:00, it's going to sometimes show up to 4:59:60? Leap minutes? Oh please. I'm gonna have to account for green martians next... DSL -- Con te partiro, su navi per mari Che io lo so, no, no non esistono piu Con te io li vivro. (Sartori F, Quarantotto E)
On Sun, 2002-09-15 at 17:15, David Lloyd wrote: > > Casey, > > > > There are actually 61 seconds in some minutes. In order to accommodate > > > leap seconds, PostgreSQL allows this to happen -- similarly to how it > > > will also allow 366 days in some years. > > > > How then, am I supposed to explain to a web interface user that when they just > > entered 5:00:00, it's going to sometimes show up to 4:59:60? > > Leap minutes? Oh please. I'm gonna have to account for green martians > next... Well, there *are* leap minutes and leap seconds. They just happen *so* rarely... -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
On Sun, 2002-09-15 at 10:32, Tom Lane wrote: > > On Sunday 15 September 2002 10:11 am, Rod Taylor wrote: > >> On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote: > >>> I don't have a clue why it's doing this - has anyone else seen this sort > >>> of behavior, or know why it might be doing it? It shows times wrong, for > >>> instance, it shows 00:04:60 where it should show 00:05:00. See below: > >> > >> There are actually 61 seconds in some minutes. In order to accommodate > >> leap seconds, PostgreSQL allows this to happen -- similarly to how it > >> will also allow 366 days in some years. > > True but irrelevant -- PG does not do accounting for leap seconds. For some reason I thought it did accommodate it when I was still using 7.1. Ahh well, if the bug is fixed, then it's all good. -- Rod Taylor
On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote: > I don't have a clue why it's doing this - has anyone else seen this sort of > behavior, or know why it might be doing it? It shows times wrong, for > instance, it shows 00:04:60 where it should show 00:05:00. See below: There are actually 61 seconds in some minutes. In order to accommodate leap seconds, PostgreSQL allows this to happen -- similarly to how it will also allow 366 days in some years. -- Rod Taylor
On Sun, 2002-09-15 at 09:51, Rod Taylor wrote: > On Sun, 2002-09-15 at 10:32, Tom Lane wrote: > > > On Sunday 15 September 2002 10:11 am, Rod Taylor wrote: > > >> On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote: > > >>> I don't have a clue why it's doing this - has anyone else seen this sort > > >>> of behavior, or know why it might be doing it? It shows times wrong, for > > >>> instance, it shows 00:04:60 where it should show 00:05:00. See below: > > >> > > >> There are actually 61 seconds in some minutes. In order to accommodate > > >> leap seconds, PostgreSQL allows this to happen -- similarly to how it > > >> will also allow 366 days in some years. > > > > True but irrelevant -- PG does not do accounting for leap seconds. > > For some reason I thought it did accommodate it when I was still using > 7.1. > > Ahh well, if the bug is fixed, then it's all good. In a subsequent post, Tom Lane said that 7.2.2 fixed the problem, but a reply post from the original poster says that he is, in fact, using 7.2.2... -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
Ron Johnson <ron.l.johnson@cox.net> writes:
> On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote:
>> I don't have a clue why it's doing this - has anyone else seen this sort
>> of behavior, or know why it might be doing it? It shows times wrong, for
>> instance, it shows 00:04:60 where it should show 00:05:00. See below:
> In a subsequent post, Tom Lane said that 7.2.2 fixed the problem,
> but a reply post from the original poster says that he is, in fact,
> using 7.2.2...
Further investigation showed that he'd built Postgres with -ffast-math
gcc option, which is well known to break the datetime rounding code :-(
regards, tom lane