The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.


Results 1 to 12 of 12

Thread: Col for Date and another one for Time, or one column for both?

  1. #1

    Col for Date and another one for Time, or one column for both?

    Hey guys!

    I don't have too much experience handling time so my question may seem very basic.

    I'm going to start adding events to the calendar class I'm writing. It's going to get the events from a MySQL time:
    • name of event
    • date of event
    • time of event
    • description of event
    • duration of event
    • category of event
    • etc


    My question is... should I save the TIME and DATE of the event in two different columns or would it be easier to just have the TIME and DATE of the event in ONE column?
    "You are playing a very dangerous game"
    "Change always is"

    Creativo Development

  2. #2
    1 TIMESTAMP column will do the job well.

  3. #3
    so you recommend using EPOCH then?
    "You are playing a very dangerous game"
    "Change always is"

    Creativo Development

  4. #4
    Make the time 'TIMESTAMP' and the default value 'CURRENT_TIMESTAMP'.

  5. #5
    I agree with blazes, 1 timestamp column will do.

  6. #6
    Keep in mind that TIMESTAMPs have a limited range of roughly 1970 to 2038. If you need a wider range of dates, I suggest you use the DATETIME type instead (1000 to 9999). Also, remember that TIMESTAMP types will automatically perform timezone correction based on the server's current timezone while DATE(TIME) types will not. See MySQL's reference manual on DATE types for more information.
    Wait, what?

  7. #7
    Quote Originally Posted by Voetsjoeba View Post
    Keep in mind that TIMESTAMPs have a limited range of roughly 1970 to 2038. If you need a wider range of dates, I suggest you use the DATETIME type instead (1000 to 9999). Also, remember that TIMESTAMP types will automatically perform timezone correction based on the server's current timezone while DATE(TIME) types will not. See MySQL's reference manual on DATE types for more information.
    I think TIMESTAMP may do, the class is intended for an appointments calendar so as long as its the near future it's fine
    "You are playing a very dangerous game"
    "Change always is"

    Creativo Development

  8. #8
    Quote Originally Posted by blazes View Post
    Make the time 'TIMESTAMP' and the default value 'CURRENT_TIMESTAMP'.
    Ok then, so let's say I want to input an appointment for November 25, 2009 at 11:00 am

    You're saying I should use TIMESTAMP, so then I would input into the database: strtotime('2009-11-25 11:00:00')

    Why would it be better to input the TIMESTAMP(1259175600) instead of the actual date and time(2009-11-25 11:00:00)???

    I'm sorry the question may seem simple, but I haven't handled time extensively.
    "You are playing a very dangerous game"
    "Change always is"

    Creativo Development

  9. #9
    Timestamps are a lot easier to work with. If you have 2009-11-25 11:00:00, but want to display "November 25th, 2009" it's a lot more work than if you used a timestamp because otherwise you have to convert 2009-11-25 11:00:00 to a timestamp, then do the editing.

  10. #10
    And remember timestamps can be longer then 1970 - 2038 with an extra column in your database.

  11. #11
    Quote Originally Posted by blazes View Post
    Timestamps are a lot easier to work with. If you have 2009-11-25 11:00:00, but want to display "November 25th, 2009" it's a lot more work than if you used a timestamp because otherwise you have to convert 2009-11-25 11:00:00 to a timestamp, then do the editing.
    Not entirely true, that used to be the case

    PHP Code:
    $datetime = new DateTime('2009-11-25 11:00:00');
    echo 
    $datetime->format('F dS, Y'); 
    Code:
    November 25th, 2009
    PHP Code:
    echo date('F dS, Y'1259146800); 
    Code:
    November 25th, 2009

    http://www.php.net/manual/en/class.datetime.php

    In this example a DATETIME is far superior to TIMESTAMP do to it's much larger date range. Also easier to manipulate the date compared to trying to manipulate a timestamp:

    PHP Code:
    date_add($datetime, new DateInterval('P10D'));
    echo 
    $datetime->format('F dS, Y'); 
    Code:
    December 05th, 2009
    as well as it's far easier to read the raw data from the DB. Granted you have to have PHP 5.3 or greater, but there's no reason you shouldn't be able to get that on your server, and most good hosts have it already. At the very least create some sort of function that can imitate the native datetime class, if needed/wanted.
    Let us live so that when we come to die even the undertaker will be sorry. - Mark Twain
    Don't PM me your CSS, xHTML, JS or PHP questions. I will not reply to ANY IE6 questions.

  12. #12
    I was practicing with the new DateTime PHP class months ago, so now I will need to review it again.

    Need some more practice with OOP. The Calendar class I'm writing is not totally OO, but it's heading that way.

    Thanks a lot for your comments guys!
    "You are playing a very dangerous game"
    "Change always is"

    Creativo Development

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012