Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 10 of 10

Thread: NOW() function

  1. #1
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    I'm trying to create this simple table to store a user's username, and a start and end time for an action that they do:

    CREATE TABLE `travel` (
    `user_name` varchar(32) default NULL,
    `start_time` datetime default NOW(),
    `end_time` datetime default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

    But I get this error message: invalid default value for 'start_time'.
    I am using phpminiadmin, not phpmyadmin, is this the problem? I really would like to keep using phpminiadmin if possible.

    ~Thanks~

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    You have to use the CURRENT_TIMESTAMP constant in DDL:

    `start_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    Also important to use a TIMESTAMP column, as DATETIME can't have a dynamic value in MySQL, and important to know that you can only have one CURRENT_TIMESTAMP field in each table. NOW() is only for use in DML, e.g. SELECT * FROM my_table WHERE start_time < NOW();

  4. #3
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    Thanks. Should I still use datetime for the end_time column though?

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Nah. Timestamp is fine. It's a unix timestamp ie Julian date. Perfect for any date after 1970. No good for birth dates or historical dates or anything requiring more than second accuracy eg milli.

  6. #5
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    What should default value be for end_time? This will always be different, for each user and each time they travel. What I'm wanting to do, is insert a record into the table when they start, and then delete the record when they're done. I can't do NULL, it says invalid default value again.

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    No default. Make start_time not nullable and end_time nullable with no default - it will default to null if no value is supplied.

    `start_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `end_time` TIMESTAMP NULL,

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    By the way, technically, the proper design for your travel log table would be like this:

    Code:
    CREATE TABLE travel (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL REFERENCES user(id),
      activity_type_id NOT NULL REF?ERENCES activity_type(id),
      activity_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ) TYPE=InnoDB;
    That's a 3rd-normal form design with one timestamp per activity, and every activity recorded when it occurs. Start and End are only 2 types of activities. This allows you to expand your design into other types of activities as well, and maintain a linear log of user actions.

  9. #8
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    Well, I'm going to need to store an end time. This is for an online game, for when a user travels to another city. I didn't want them to be able to instantly appear in the new city, so I have an algthorium to calculate the time, distance and cost between each of the two cities. What I was wanting to do, is when their travel time is up (end_time), enable a button for them to click to end their trip and arrive in the new city. But if end_time had not yet passed, either disable the button or display an error message. So I don't think I would ever need to expand the table for other activities.

    Thanks for the help, I got the table created. Now to see how I'm going to calculate the end time..

  10. #9
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Unknown98 - Talking about NERO or a purely web based game?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  11. #10
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    Honestly never heard of NERO, so I'm going to say purely web-based .


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 12:09 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com