SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2010
    Posts
    197
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    timestamp data type looks like a date time

    For a 'date_added' column, I thought it was always better to use a datetime data type instead of timestamp.

    In MySql 5.0 I read there is no default if you use the datetime data type ( is that true? ). I want a default, so I switched to timestamp datatype. Which looks to be stored as a date time. I made 1 test record and the results of a default, in a timestamp data type is: 2013-08-24 13:16:12.

    Does all of this sound correct? A timestamp data type saves this 2013-08-24 13:16:12, instead of the timestamp I expected i.e. 10982304982039482

  2. #2
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,617
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    http://dev.mysql.com/doc/refman/5.0/en/datetime.html

    The DATETIME type stores data in a readable format with no range limitations.

    The TIMESTAMP type stores data in a UNIX timestamp, which has a limited range. However, it also converts the inserted timestamp to UTC before insertion, and automatically converts it to the server's timezone when retrieved (something the DATETIME type does not do).

    You can use DEFAULT on the column to specify a default date/time. If the column allows null values, it will default to null. Or, if the column value is not nullable, MySQL will automatically use the current date & time as the default value.

    http://dev.mysql.com/doc/refman/5.0/...-defaults.html
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Force Flow View Post
    The DATETIME type stores data in a readable format
    nope

    http://dev.mysql.com/doc/internals/e...sentation.html

    Quote Originally Posted by Force Flow View Post
    Or, if the column value is not nullable, MySQL will automatically use the current date & time as the default value.
    only for TIMESTAMP, not for DATETIME, and only for the first timestamp column in the tab;e
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2010
    Posts
    197
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I slightly misspoke.

    I can put a hard coded default in a datetime data type column, but mysql 5.0 has the option for dynamic ( if you will ) default if I use a timestamp data type.

    I was just surprised to see the timestamp data type produce this:

    2013-08-24 13:16:12

    instead of this

    10982304982039482

    I thought I did something wrong, or had missed a better solution.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sessions View Post
    I was just surprised to see the timestamp data type produce this:
    2013-08-24 13:16:12

    instead of this

    10982304982039482
    did you happen to see how mysql stores DATETIME values?

    hint: they are ~not~ stored as yyyy-mm-dd strings

    similarly, timestamp values are not stored as yyyy-mm-dd hh:mm:ss strings

    instead, they are stored as unix epoch integers, and when you select them, they are just displayed as yyyy-mm-dd hh:mm:ss strings

    moral of the story: internal storage format is often quite different from displayed format
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •