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 6 of 6
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    73
    Thanks
    19
    Thanked 1 Time in 1 Post

    Matching Dates in Database For Calendar

    I'm creating a very basic calendar where each day's event's are loaded in from the database. My original thought was just to compare the current day of the calendar to the day in the database, and just take all that info and populate that day. But then I realized that the dates in the database are to be stored as a TIMESTAMP (2013-01-01 00:00:01) so if I take the current day when the calendar is being populated in this format 2013-01-01 and try to match it to dates in the database in the timestamp format, that won't match up...right? Is there a way around this problem so it'll only match up the day not the actual time in hours?

  • #2
    New Coder
    Join Date
    Dec 2012
    Location
    USA
    Posts
    82
    Thanks
    3
    Thanked 17 Times in 17 Posts
    wrong
    Last edited by TFlan; 01-10-2013 at 07:30 PM. Reason: wrong

  • Users who have thanked TFlan for this post:

    cgdtalent (01-10-2013)

  • #3
    New Coder
    Join Date
    Jan 2013
    Posts
    73
    Thanks
    19
    Thanked 1 Time in 1 Post
    Ok thanks...that makes a lot of sense.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    *** WRONG ANSWER *** (or at least not a very efficient or simple answer)

    MUCH easier:
    Code:
    SELECT * FROM yourtable WHERE DATE(yourtimestampfield) = '2013-1-13'
    Or, if you want to match to *today*, it is even simpler:
    Code:
    SELECT * FROM yourtable WHERE DATE(yourtimestampfield) = CURDATE()
    The DATE( ) function in MySQL extracts the data alone from a date and time field.

    So, for example, it converts '2013-1-22 17:13:12' to just '2013-1-22' for you.
    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.

  • Users who have thanked Old Pedant for this post:

    cgdtalent (01-10-2013)

  • #5
    New Coder
    Join Date
    Jan 2013
    Posts
    73
    Thanks
    19
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    *** WRONG ANSWER *** (or at least not a very efficient or simple answer)

    MUCH easier:
    Code:
    SELECT * FROM yourtable WHERE DATE(yourtimestampfield) = '2013-1-13'
    Or, if you want to match to *today*, it is even simpler:
    Code:
    SELECT * FROM yourtable WHERE DATE(yourtimestampfield) = CURDATE()
    The DATE( ) function in MySQL extracts the data alone from a date and time field.

    So, for example, it converts '2013-1-22 17:13:12' to just '2013-1-22' for you.
    Oh ok...this is great! Thanks for clarifying that!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    By the by, when comparing dates and times, MySQL does *NOT* require that you use zero padding on months and days (and hours/minutes/seconds) that are single digits.

    That is, it sees '2013-1-7' as the same as '2013-01-07'. MySQL does *NOT* actually store dates and times in that format (as strings). It always converts the string formats to the internal format before doing calculations, comparisons, etc., or before storing a value in the DB>
    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
    •