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 14 of 14
  1. #1
    New Coder
    Join Date
    May 2009
    Posts
    36
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Right way to store time stamp?

    A small clarification please.
    is getting time in php as
    Code:
    time()
    and then storing in mysql database as datatype
    Code:
    TIMESTAMP
    is the best way to store time relevant info such as blog post etc?.

  • #2
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,133
    Thanks
    12
    Thanked 332 Times in 328 Posts
    a TIMESTAMP is sufficient, but you also could use DATE or DATETIME, what’s unnecessary is the time() call. MySQL brings its own time setting method, which you could simply call in your query.
    PHP Code:
    INSERT
    -- etc.
    SET 
        
    `date` = NOW() 
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #3
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts
    If you don't need to do advanced manipulation of your timestamp field with sql, just use an unsigned int...if you use one of the other data types then sql is going to convert it into some format you may not like...which you'll then have to most likely convert back. Unix timestamps are raw and simple and I always keep them that way.

  • Users who have thanked bacterozoid for this post:

    verdafun (03-01-2010)

  • #4
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    I stick with standard mysql NOW() function and mysql date format ie yyyy-mm-dd hh:mm:ss

    its easy to work with and doesn't involve any insane maths like a unix time stamp does
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #5
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by bacterozoid View Post
    If you don't need to do advanced manipulation of your timestamp field with sql, just use an unsigned int...if you use one of the other data types then sql is going to convert it into some format you may not like...which you'll then have to most likely convert back. Unix timestamps are raw and simple and I always keep them that way.
    Pretty much my opinion too. Plus, you know that dumping a time() value in an int field is going to work with any DB type. No arsing around trying to find out what syntax a specific DB maybe using for a time function. Unix timestamp can also be formatted in any style on output too, so you ain't stuck with some format you can't easily change at some point down the line.

  • #6
    New Coder
    Join Date
    May 2009
    Posts
    36
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks for the suggestion.
    This is slightly out of topic, what is the best way to format it and show it the user - php or directly using mysql's format functions?

  • #7
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts
    Think of your database as nothing more than a stupid storage engine. It can certainly do other stuff, but you don't want it to.

    Use PHP's date() function to format the timestamp to your liking. That way, if you ever need to change database types, you're not going to have to look for equivalent functions.

  • Users who have thanked bacterozoid for this post:

    verdafun (03-01-2010)

  • #8
    New Coder
    Join Date
    May 2009
    Posts
    36
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bacterozoid View Post
    Think of your database as nothing more than a stupid storage engine. It can certainly do other stuff, but you don't want it to.

    Use PHP's date() function to format the timestamp to your liking. That way, if you ever need to change database types, you're not going to have to look for equivalent functions.
    Thanks again, especially for "stupid storage engine".
    it is still unclear to me why one should use an unsigned int...wont that make years older than 1970 impossible to store?

  • #9
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,133
    Thanks
    12
    Thanked 332 Times in 328 Posts
    Quote Originally Posted by verdafun View Post
    it is still unclear to me why one should use an unsigned int...wont that make years older than 1970 impossible to store?
    you know that the unix time stamp starts at 01/01/1970 ?
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #10
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Using a UNIX TIMESTAMP is fine for a generic timestamp, as long as that's all you use it for, a unique identifier for that record. If you want to actually use that value to perform date calculations or actually store a date value, use a DATE type field. Be sure you understand that the UNIX / POSIX timestamp values carry the limitation that they are only good for values between Jan 1, 1970 and Jan 19, 2038 (on 32 bit systems, which are most common).

    Although using a UNIX TIMESTAMP with an INT type field is a more platform agnostic and portable way to store the date, I personally almost always use the MySQL TIMESTAMP type and set the default value to CURRENT_TIMESTAMP, so when you perform an insert on the table, it automagically sets the current date / time value for you, without even having to reference the field name. You can also set the ON UPDATE to update this value on any successive record updates. The plus side here is that this is a value that MySQL can use to perform any date calculation or manipulation, with the many DATE and TIME functions that are inherent in MySQL. Want to convert this value to a UNIX TIMESTAMP? There's a function for that.

    Your best bet is to check the MySQL reference manual.

  • #11
    New Coder
    Join Date
    May 2009
    Posts
    36
    Thanks
    5
    Thanked 0 Times in 0 Posts

    resolved

    bdl : thanks, iam now using your idea as i feel it is best suggested here.
    Last edited by verdafun; 03-03-2010 at 09:53 AM.

  • #12
    New Coder
    Join Date
    May 2009
    Posts
    36
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Post how do i set "resolve" to this thread?

    you know, the green text..

  • #13
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,133
    Thanks
    12
    Thanked 332 Times in 328 Posts
    Guidelines #6
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #14
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,801
    Thanks
    160
    Thanked 2,216 Times in 2,203 Posts
    Blog Entries
    1
    how do i set "resolve" to this thread?
    See the last item at http://www.codingforums.com/postguide.htm
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)


  •  

    Posting Permissions

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