Thread: CURRENT_DATE and CURRENT_TIME return incorrect values
I have a table with these columns:
order_date date DEFAULT CURRENT_DATE
order_time time with time zone DEFAULT CURRENT_TIME
setup_date date
last_update date DEFAULT CURRENT_DATE
The order_date and last_update should always be identical because I let
pgsql fill in those fields when a new row is added. Additionally,
setup_date should be identical as well, because my application just
queries the server time (same exact server as pgsql is running on).
However today I noticed something strange: a row was added with these
values:
order_date | order_time | setup_date | last_update
-----------+--------------------+------------+------------
2003-05-26 | 02:22:00.166015-04 | 2003-05-28 | 2003-05-26
Which is very odd because a few minutes later I ran a manual query that
returned this:
dev=> SELECT current_date, current_time;
date | timetz
-----------+--------------------
2003-05-28 | 13:19:39.189404-04
I also checked my apache log files to make sure that the server hadn't
skipped a few days for some reason... But that wasn't the case, and my
logs show hits for the 26th, 27th and 28th, as it should be.
I then went back to my application and made it create a new record. The
following row was created:
order_date | order_time | setup_date | last_update
-----------+--------------------+------------+------------
2003-05-28 | 13:25:12.126979-04 | 2003-05-28 | 2003-05-28
What you may find interesting is that my DB had been mostly dormant for
the past several days. In other words, only a few SELECT queries had
been executed, and no INSERT, UPDATE, DELETE or VACUUM operations had
been run. I have no idea if this is significant or not...
My environment is:
pgsql 7.3.2
Debian/Linux 3.0 (i386)
/etc/timezone is 'US/Eastern'
libdbi-perl 1.21-2
libdbd-pg-perl 1.01-3
No defaults in postgresql.conf were changed except for
'unix_socket_directory'. The Locale is set to 'C'.
I noticed that there are several entries in the HISTORY file for pgsql
7.3.3 that deal with dates and times. Would upgrading fix my problem,
or is this something entirely different?
Did you create the table on 2003-05-26 by any chance? It appears that the CURRENT_DATE/CURRENT_TIME in your create table statement got interpreted, and replaced, so every record that ever gets inserted will have the same date and time. use a \d table_name in psql to confirm. You should be able to alter the table/columns and correct the problem. valerian wrote: >I have a table with these columns: > > order_date date DEFAULT CURRENT_DATE > order_time time with time zone DEFAULT CURRENT_TIME > setup_date date > last_update date DEFAULT CURRENT_DATE > >The order_date and last_update should always be identical because I let >pgsql fill in those fields when a new row is added. Additionally, >setup_date should be identical as well, because my application just >queries the server time (same exact server as pgsql is running on). > >However today I noticed something strange: a row was added with these >values: > > order_date | order_time | setup_date | last_update > -----------+--------------------+------------+------------ > 2003-05-26 | 02:22:00.166015-04 | 2003-05-28 | 2003-05-26 > >Which is very odd because a few minutes later I ran a manual query that >returned this: > > dev=> SELECT current_date, current_time; > date | timetz > -----------+-------------------- > 2003-05-28 | 13:19:39.189404-04 > >I also checked my apache log files to make sure that the server hadn't >skipped a few days for some reason... But that wasn't the case, and my >logs show hits for the 26th, 27th and 28th, as it should be. > >I then went back to my application and made it create a new record. The >following row was created: > > order_date | order_time | setup_date | last_update > -----------+--------------------+------------+------------ > 2003-05-28 | 13:25:12.126979-04 | 2003-05-28 | 2003-05-28 > >What you may find interesting is that my DB had been mostly dormant for >the past several days. In other words, only a few SELECT queries had >been executed, and no INSERT, UPDATE, DELETE or VACUUM operations had >been run. I have no idea if this is significant or not... > >My environment is: > >pgsql 7.3.2 >Debian/Linux 3.0 (i386) >/etc/timezone is 'US/Eastern' >libdbi-perl 1.21-2 >libdbd-pg-perl 1.01-3 > >No defaults in postgresql.conf were changed except for >'unix_socket_directory'. The Locale is set to 'C'. > >I noticed that there are several entries in the HISTORY file for pgsql >7.3.3 that deal with dates and times. Would upgrading fix my problem, >or is this something entirely different? > > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > >
On Thu, May 29, 2003 at 07:57:39PM -0500, DeJuan Jackson wrote:
> Did you create the table on 2003-05-26 by any chance?
Nope, that table has been around for about a month or so. I haven't even
made any recent changes to it.
> It appears that the CURRENT_DATE/CURRENT_TIME in your create table
> statement got interpreted, and replaced, so every record that ever gets
> inserted will have the same date and time.
> use a \d table_name in psql to confirm.
> You should be able to alter the table/columns and correct the problem.
The \d output looks like this:
Column | Type | Modifiers
------------------+---------------------+-----------------------------------------------
order_date | date | default date('now'::text)
order_time | time with time zone | default ('now'::text)::time(6) with time zone
setup_date | date |
last_update | date | default date('now'::text)
I think this is correct? I want pgsql to enter the current date/time in
by default. It normally seems to work ok, except that one time a couple
days ago when it entered '2003-05-26' instead of '2003-05-28' for some
reason that I don't understand. Especially considering that it entered
the correct date/time when I added another row just a few minutes later...
It's very strange. I think it would be difficult to reproduce this
behavior because my DB had been sitting around mostly idle for some
time, and I've never seen this happen before.
I'm fairly certain the problem is related to pgsql because my system
time has been accurate all along. In other words, the system time
didn't jump from 2003-05-26 to 2003-05-28 in one instant. My
apache logs show regular hits for that date range, and so do the other
system logs (ie, /var/log/messages has normal, regular entries).
The only thing I can think of is that pgsql cached an old date and time,
for some reason, and used that for the first record, and then actually
queried the system's date/time after that. But that sounds pretty
weird...
I really need for the order_date to be 100% accurate though, because my
application must do calculations based on that. So I'm thinking about
having have it grab the date/time directly from the system clock and
enter it instead of leaving that to pgsql. Unless anyone can point out
where I made an error.
The big question is: Can you repeat it?
The next big question is: Can you supply a complete example of how to repeat
it.
The only things that crossed my mind were a) you started a transaction on the
26th and only wrote that record on the 28th or b) which ever system you were
querying the time of to get the 28th had a different time to the database
server.
Is there some reason you are splitting the order time and date into two columns
rather than using a single timestamp one?
--
Nigel Andrews
On Fri, 30 May 2003, valerian wrote:
> On Thu, May 29, 2003 at 07:57:39PM -0500, DeJuan Jackson wrote:
> > Did you create the table on 2003-05-26 by any chance?
>
> Nope, that table has been around for about a month or so. I haven't even
> made any recent changes to it.
>
> > It appears that the CURRENT_DATE/CURRENT_TIME in your create table
> > statement got interpreted, and replaced, so every record that ever gets
> > inserted will have the same date and time.
> > use a \d table_name in psql to confirm.
> > You should be able to alter the table/columns and correct the problem.
>
> The \d output looks like this:
>
> Column | Type | Modifiers
> ------------------+---------------------+-----------------------------------------------
> order_date | date | default date('now'::text)
> order_time | time with time zone | default ('now'::text)::time(6) with time zone
> setup_date | date |
> last_update | date | default date('now'::text)
>
> I think this is correct? I want pgsql to enter the current date/time in
> by default. It normally seems to work ok, except that one time a couple
> days ago when it entered '2003-05-26' instead of '2003-05-28' for some
> reason that I don't understand. Especially considering that it entered
> the correct date/time when I added another row just a few minutes later...
>
> It's very strange. I think it would be difficult to reproduce this
> behavior because my DB had been sitting around mostly idle for some
> time, and I've never seen this happen before.
>
> I'm fairly certain the problem is related to pgsql because my system
> time has been accurate all along. In other words, the system time
> didn't jump from 2003-05-26 to 2003-05-28 in one instant. My
> apache logs show regular hits for that date range, and so do the other
> system logs (ie, /var/log/messages has normal, regular entries).
>
> The only thing I can think of is that pgsql cached an old date and time,
> for some reason, and used that for the first record, and then actually
> queried the system's date/time after that. But that sounds pretty
> weird...
>
> I really need for the order_date to be 100% accurate though, because my
> application must do calculations based on that. So I'm thinking about
> having have it grab the date/time directly from the system clock and
> enter it instead of leaving that to pgsql. Unless anyone can point out
> where I made an error.
>
valerian <valerian2@hotpop.com> writes:
> The only thing I can think of is that pgsql cached an old date and time,
> for some reason, and used that for the first record, and then actually
> queried the system's date/time after that.
Is it possible that the entry was made from a transaction that had been
sitting open for two days? now() and related functions return
transaction start time.
regards, tom lane
Nigel J. Andrews wrote: > The big question is: Can you repeat it? I doubt it, because I never noticed this before. And I tend to check all the data very closely because I'm doing developement work (gotta catch all those bugs). > The next big question is: Can you supply a complete example of how to repeat > it. Alas no. :( I tried to reproduce the oddity by letting my system sit mostly idle for the past 24 hours, to see if the same thing would happen. I then added a new record, but the date/time was correct. > Is there some reason you are splitting the order time and date into two columns > rather than using a single timestamp one? Yes, the order_time is sort of an 'extra' field. I don't currently use it, and it might even disapear in the future. I only do calculations on the order_date and setup_date fields. That's why I didn't use a timestamp. > The only things that crossed my mind were a) you started a transaction on the > 26th and only wrote that record on the 28th or b) which ever system you were > querying the time of to get the 28th had a different time to the database > server. Tom Lane wrote: > Is it possible that the entry was made from a transaction that had been > sitting open for two days? now() and related functions return > transaction start time. Aha! You may be on to something there. First, to clarify: I only have one server, and all processes (pgsql, apache, etc.) are running on it and query that server's system clock. When I run 'ps auxww |grep postgres' it shows: postgres 4770 0.0 0.2 7068 3076 pts/3 S Apr27 0:11 /usr/local/pgsql/bin/postmaster postgres 12525 0.0 0.2 8060 3024 pts/3 S Apr27 0:00 postgres: stats buffer process postgres 5850 0.0 0.2 7116 3080 pts/3 S Apr27 0:00 postgres: stats collector process postgres 20284 0.0 0.8 10556 8748 pts/3 S May02 2:11 postgres: dev dev [local] idle postgres 8572 0.0 0.4 7484 4772 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction postgres 20062 0.0 0.4 7484 4680 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction postgres 18226 0.0 0.4 7484 4680 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction postgres 16750 0.0 0.4 7484 4680 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction postgres 25970 0.0 0.4 7484 4676 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction postgres 21388 0.0 0.4 7484 4676 pts/3 S 05:56 0:00 postgres: dev dev [local] idle in transaction PID 20284 is an instance of psql, that I always keep open in 'screen', so that's why it's been running since May 02. All the other children processes are managed by mod_perl, using Apache::DBI to get persistent connections. My application was completely dormant during the 'ps' snapshot above. But it does seem that Apache::DBI starts a transaction (one for each mod_perl process) and waits... I'm thinking that by some freaky occurence, one of those transactions got started on the 26th and didn't get used at all (not even for SELECT queries) during those few days, until the 28th when it was handed the INSERT for the row in question. Does that sound possible? It seems strange because my application has to run a good half-dozen SELECT queries before even getting to the point of adding a record, and there were a few (but only a few) other queries done during those days as well. I imagine that this situation shouldn't happen in a real environment where the DB is actually used on a daily basis, but there is still a chance for the dates to be wrong if a transaction starts a little before midnight but doesn't commit until the next day. And chances are the times may often tend to be off by a little? If this is true, then it sounds like a very bad idea to use columns with 'default CURRENT_DATE' and 'default CURRENT_TIME' with Apache::DBI...