SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist
    Join Date
    Jul 2004
    Location
    USA
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting Field TIMESTAMP is TODAY

    Hello,

    I have a query that runs perfectly, but I want to add a WHERE parameter so its checks the field `thehour` (which is timestamp) and makes sure it equals TODAY and only return the ones for today.

    Could this be done?
    BKerr

  2. #2
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course it depends which version of SQL you use. And it's not quite right forum to post this. But I try to help you. Try this code:

    Code:
    SELECT * FROM table WHERE thehour=UNIX_TIMESTAMP(CURRENT_DATE())

  3. #3
    SitePoint Evangelist
    Join Date
    Jul 2004
    Location
    USA
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I ran that and it didnt return anything.

    The field thehour is set to timestamp(14)...
    BKerr

  4. #4
    Non-Member
    Join Date
    Nov 2003
    Location
    here
    Posts
    258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i find the best method is to just use an int(11) then use time() to insert a timestamp. that way its easy as you can just say WHERE timestamp < time() - 24*60*60

  5. #5
    SitePoint Enthusiast weekyd's Avatar
    Join Date
    Jun 2004
    Location
    UK
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i had to make a query similar to yours. after looking at mysql time function which is a lot of fun i came up with this to filter out my datas only for today (24 hrs)

    WHERE dayofmonth( ..your timestamp field.. ) = dayofmonth( current_date( ) )

    you could ofcourse do it in a diffway...
    WHERE "your timestamp field" >=$date1 AND "your timestamp field" < $date2

    i used that to populate the datas for any given date..$date1 and $date 2 can be passed through a drop down or any other method u prefere

    hope it helps

  6. #6
    SitePoint Evangelist
    Join Date
    Jul 2004
    Location
    USA
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by weekyd
    i had to make a query similar to yours. after looking at mysql time function which is a lot of fun i came up with this to filter out my datas only for today (24 hrs)

    WHERE dayofmonth( ..your timestamp field.. ) = dayofmonth( current_date( ) )

    you could ofcourse do it in a diffway...
    WHERE "your timestamp field" >=$date1 AND "your timestamp field" < $date2

    i used that to populate the datas for any given date..$date1 and $date 2 can be passed through a drop down or any other method u prefere

    hope it helps
    This worked perfectly, would there be a current_date type thing for the month, like current_month?
    BKerr

  7. #7
    SitePoint Evangelist
    Join Date
    Jul 2004
    Location
    USA
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does anyone know how I would do this by month?

    I have pretty much the same query but I only want to display the rows where `thehour` (timestamp(14)) is equal to this MONTH.
    BKerr

  8. #8
    SitePoint Enthusiast weekyd's Avatar
    Join Date
    Jun 2004
    Location
    UK
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    SELECT MONTHCURRENT_DATE( ) )
    AS 
    current_monthHOURCURRENT_TIME( ) )AS current_hour 
    you have to fiddle about with your db field..the key functions here are:
    MONTH( CURRENT_DATE( ) )
    HOUR( CURRENT_TIME( ) )

    ..u have to match with whatver you want..like
    WHERE "your timestamp" = MONTH( CURRENT_DATE( ) ) AND "your timestamp" = HOUR( CURRENT_TIME( ) )

    hope it helps


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
  •