NOW() recording different times

My INSERT uses the NOW() function to grab the system time and fill in 2 different fields. The time it records is sometimes a few minutes different, and sometimes over an hour different. The query looks something like this:

INSERT INTO members (name, birth_date, date_created, signup_date) VALUES ('$name', '$birth_date', NOW(), NOW())

The time should be exactly the same, and it often is. This form gets maybe a thousand new records a day. Is the server too busy?

are you serious? two instances of NOW() in the same sql statement like in your example?

i’m having a hard time believing this…

Do you have a separate table for recording signups?

r937: Can you explain? And could that cause the issue I described above?

SpacePhoenix: We record the member (using the NOW() like above), and if the query returns an affected row, we record the order using the newly created member_id.

i’m not sure what you want me to explain

you gave the problem like this –

the time should be exactly the same, full stop

i have a hard time believing that if this INSERT with the two NOW()s executes successfully that the two times are different

I see, I thought you suggested that writing 2 instances of NOW() in the same statement was bad syntax.

The trouble surfaced when we found about 20 records (of about 50,000) which retrieved a bad date. The process is:

  1. use PHP to get the current date: date(‘Y-m-j’)
  2. add the number of months to the current date (it’s a magazine subscription) creating $member_exp, which is the all-important expiration date of the subscription.

This was returning the old 12/31/1969. It then correctly adds the number of months to that date. Next I insert the data using the following statement:

$q = "INSERT
							INTO members
							(name,
							 birth_date,
							 phone, 
							 email,
							 spouse_name,
							 spouse_birth_date,
							 spouse_email, 
							 ship_street,
							 ship_city,
							 ship_state,
							 ship_zip,
							 intl_ship_street,
							 intl_ship_city,
							 intl_ship_zip,
							 intl_ship_country,
							 member_since,
							 exp_date,
							 recent_order_date,
							 status,
							 magazine,
							 mailing_list,
							 date_created)
						VALUES
							('$name', 
							 '$birth_date', 
							 '$phone', 
							 '$email', 
							 '$spouse_name', 
							 '$spouse_birth_date', 
							 '$spouse_email', 
							 '$ship_street',
							 '$ship_city',
							 '$ship_state',
							 '$ship_zip',
							 '$intl_ship_street',
							 '$intl_ship_city',
							 '$intl_ship_zip',
							 '$intl_ship_country',
							 '$date',
							 '$member_exp',
							 NOW(),
							 'active',
							 '1',
							 '1',
							 NOW())";

In only a handful of records, the 2 instances of NOW() are recording very different times. Sometimes a few minutes apart, other times a few hours apart!

i find this really hard to believe

here’s my advice: fix those few rows, and forget it ever happened

Was it a one time incident or continuous problem?

Make sure you don’t fill one of the dates from PHP.
What version is your MySQL?

Unfortunately it keeps happening. Between SEPT 1 and today it’s been 23 times. It’s the kind of issue I can’t tell my customer to ignore. It is consistent: the records that show a discrepancy in the date/times recorded with NOW(), also show a bad date/time gotten from PHP.

MySQL Server version: 5.1.45-community
MySQL client version: 5.0.51a

Do you recommend continuing to use NOW() to get the current date/time, rather than trying to put date(‘Y-m-j’) to a variable and use that to record the time?

I’d be surprised if changing the approach made any difference because in each case you’d be getting the time from the same place and if that place has it messed up then changing the code that reads the messed up date will not change anything.

This sounds very much like there is something wrong with the system date/time on your server.

Is the database on the same server as the web site? If not then are both servers set to the same time?

i have no idea, sorry

Since there’s not much to try, I’ve put the PHP value for the system date in place of NOW() in the query. I’ll let it run a while and see if any more strange dates come through.

Thanks for your help with this!

I have since stared using PHP to get the system time, and the problem has disappeared. There are a few monkey wrenches in my server setup. Mainly, it’s running Windows! No choice on that, but it might be the culprit, or at least part of the problem.

I realize I haven’t fixed the issue with NOW() described above. But this works:

$date = date('Y-m-j');

And in my query, I use $date instead of NOW().