SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:
    Code:
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    are you serious? two instances of NOW() in the same sql statement like in your example?

    i'm having a hard time believing this...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Do you have a separate table for recording signups?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ggeiger View Post
    r937: Can you explain?
    i'm not sure what you want me to explain

    you gave the problem like this --
    Quote Originally Posted by ggeiger View Post
    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:
    Code:
    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.
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    $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!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ggeiger View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member RonnanR's Avatar
    Join Date
    Aug 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  9. #9
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  10. #10
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,600
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ggeiger View Post
    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?
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i have no idea, sorry
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  13. #13
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    $date = date('Y-m-j');
    And in my query, I use $date instead of NOW().


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •