Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jul 2011
    Posts
    118
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Saving date/time

    Hey everyone,

    Im having a bit of difficulty getting my head around date/time and timestamps.

    What I'd like to do is every time a row is created it stores the current date and time (correct at client side) then when needs be can be displayed in the format:

    Month dd yyy hh:mm

    Is there a less round about way of doing it than running this:
    PHP Code:
    $date date("F j, Y");
    $time date("H:i");
    $time_now=mktime(date('H')-1,date('i'));
    $datetime=date('H:i',$time_now); 
    Then passing $date and $time into the mySql table?


    Thanks

    YD
    Last edited by YourDirector; 03-27-2012 at 03:19 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Yes, in your insert use now() and make the field a datetime field type.

    INSERT INTO yourtable VALUES ('something', 'somethingelse',now());

    use DATE_FORMAT in your mysql select statement to format it in the way you wish.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Actually, you can do it automatically when you create the table:

    Code:
    CREATE TABLE sometablename (
        insertTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        someText VARCHAR(100) 
    );
    
    INSERT INTO sometablename (someText) VALUES('demonstration');
    
    SELECT * FROM sometablename;
    And the insertTime field will automatically get the date and time when the record was inserted.

    I have no idea why the MySQL designers gave us two names (TIMESTAMP and DATETIME) for the same data type or why they gave us three names (CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() AND NOW() ) for the same function/value. But they did.

    And I have no idea why we can't use
    Code:
    CREATE TABLE sometablename (
        insertTime DATETIME DEFAULT NOW(), /* SYNTAX ERROR! */
        someText VARCHAR(100) 
    );
    since they are effectively synonyms, but we can't.

    Don't worry about it. Use the syntax I showed. And then treat your column as you would any DATETIME column.. It works.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    A TIMESTAMP changes its value on an update of the row whereas a DATETIME does not. So if you are sure you need the date and time the row was created, I'd go for the latter. If you want when it was created OR last updated then a TIMESTAMP would be fine.

    DATETIME can also hold values of a far significantly longer time period from AD 1000 to AD 9999 where TIMESTAMP is limited to 1970 to 2038.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Quote Originally Posted by guelphdad View Post
    A TIMESTAMP changes its value on an update of the row whereas a DATETIME does not.
    That's what I had remembered, but when I just tried it that didn't happen.

    I wonder if it depends on the version of MySQL?

    Of course, you can always do
    Code:
    UPDATE tablename SET mytimestamp=mytimestamp, anotherfield = 'whatever'
    to override the automatic updating. I recall doing that with MySQL 5.1 (using 5.5 on this machine).

    DATETIME can also hold values of a far significantly longer time period from AD 1000 to AD 9999 where TIMESTAMP is limited to 1970 to 2038.
    Well, of course true, but if you are setting the field to NOW() then it's unlikely you need to worry about the extended range.

    ******

    Anyway, the important part is that there are always options.

    I would like to shoot the idiotic MySQL designers who opted to disallow expressions in DEFAULT values. I use them all the time in SQL Server and they are *SO* handy.
    Last edited by guelphdad; 03-28-2012 at 07:51 PM. Reason: fixed quoting
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,640
    Thanks
    0
    Thanked 649 Times in 639 Posts
    My understanding is that the difference is that timestamp is supposed to be more efficient than datetime but is limited because the unix timestamp values it uses internally mean that it only works for dates after 1st Jan 1970 whereas datetime uses a less efficient internal format and can handle a far greater range of dates.

    Whether those differences are actually implemented in any given SQL database is a separate matter.

    I think the way that the DB2 book that I was using as a reference when I originally switched to working with relational databases was to use timestamp for created and last modified (with that being defined in the create statement itself) and use datetime when you want to be able to actually record specific dates and times in general where the dates are not necessarily always going to be after 1970.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    TIMESTAMP is not ANSI SQL. So whenever it is supported by a given DB, it is going to be implementation dependent.

    But, for that matter, DATETIME is not implemented the same in various DBs. And ANSI SQL only specifies a useless set of capabilities for it. So anything useful you do with it is going to need to be DB-dependent code.

    Just for example, MySQL only supports entry of datetime values in the format 'YYYY-MM-DD hh:mm:ss'.

    SQL Server supports the, but also supports (given the appropriate settings) 'MM-DD-YYYY' and 'DD-MM-YYYY'.

    And the MS JET drivers support #YYYY-MM-DD# and #MM-DD-YYYY# but *NOT* 'DD-MM-YYYY# (notice also the change in required delimiters).

    And Oracle has some really strange requirements, though I've heard that the newest versions of Oracle are more flexible.

    And let's not even talk about the various datetime functions supported by the various DBs.

    DATE() in MySQL is the same as DATEVALUE() in others. CURDATE() in MySQL is the same as DATE() in others. And date arithmetic? Fugeddaboudit. NOBODY uses the same functions!

    For all practical purposes, there aren't any useful standard in the relational DB world for dates and times.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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