SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Which is better: Date & Time in separate fields or both via DATETIME or TIMESTAMP

    Hello,

    Do you recommend that Date should be kept in a field with Type of DATE and time kept in a field with type of TIME
    or do you recommend that both values be kept together in a field of DATETIME?

    And in particular I am referring to when it comes programming the pages in say Php, is it cleaner/faster to have
    the date values separate as Date from Time values?

    Also is there any practical difference in size & speed between the field having type of DATETIME or TIMESTAMP?

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by WorldNews View Post
    Do you recommend that Date should be kept in a field with Type of DATE and time kept in a field with type of TIME
    or do you recommend that both values be kept together in a field of DATETIME?
    definitely the latter

    let's say that your table uses a single datetime column called salesdate

    to retrieve all the sales between 6 pm friday july 13 and 9 am monday july 16 would require the following WHERE clause --
    Code:
    WHERE salesdate BETWEEN '2012-07-13 18:00' AND '2012-07-16 09:00'
    now let's say that your table uses separate date and time columns

    as an exercise, try to come up with the WHERE clause yourself


    Quote Originally Posted by WorldNews View Post
    Also is there any practical difference in size & speed between the field having type of DATETIME or TIMESTAMP?
    whether there is a difference in speed is arguable (but i don't think that for practical purposes it matters much)

    however, there is a huge difference in size, and a tremendous difference in the range of values that can be stored

    of course, for full details, please see the manual
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    1st, Thanks as usual for your insight.

    So good thing I asked you, since I was about to launch this new service with DATE & TIME fields, but now I am going to go the other way.

    To be sure for the 2nd part of the question, are you saying that data type DATETIME or TIMESTAMP is better? Faster and takes less room, etc.?

    Regards,
    Dean

    Quote Originally Posted by r937 View Post
    definitely the latter
    let's say that your table uses a single datetime column called salesdate

    to retrieve all the sales between 6 pm friday july 13 and 9 am monday july 16 would require the following WHERE clause --
    Code:
    WHERE salesdate BETWEEN '2012-07-13 18:00' AND '2012-07-16 09:00'
    now let's say that your table uses separate date and time columns

    as an exercise, try to come up with the WHERE clause yourself


    whether there is a difference in speed is arguable (but i don't think that for practical purposes it matters much)

    however, there is a huge difference in size, and a tremendous difference in the range of values that can be stored

    of course, for full details, please see the manual

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by WorldNews View Post
    To be sure for the 2nd part of the question, are you saying that data type DATETIME or TIMESTAMP is better? Faster and takes less room, etc.?
    i did not, on purpose, indicate a preference

    in fact, i pointed out that they are quite different, except perhaps in terms of speed

    you really should read the manual
    r937.com | rudy.ca | 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
  •