List:General Discussion« Previous MessageNext Message »
From:Keith Ivey Date:September 27 2005 4:30pm
Subject:Re: linux timestamp
View as plain text  
Dotan Cohen wrote:
> Hi all, I have a field in a mysql database v4.0.18 that contains a
> linux timestamp. I have been googleing for a solution that would
> return to me all the entries where the timestamp falls on, say a
> wednesday, or between 2pm to 3pm. I am led to believe that it is
> possible, but I have found no examples. Something like:
> SELECT * from listings WHERE timestamp(day==wednesday)
> or
> SELECT * from listings WHERE timestamp(14:00 <= time <= 15:00)

If you're wanting to do queries like that regularly, you should set up columns 
containing the weekday and the hour and index them.  For a one-shot, you can use 
a query something like

    SELECT * FROM listings WHERE 
DATE_FORMAT(FROM_UNIXTIME(your_timestamp),'%W')) = 'Wednesday';


    SELECT * FROM listings WHERE 
DATE_FORMAT(FROM_UNIXTIME(your_timestamp),'%H')) = '14';

but it won't be fast if the table is big.

Keith Ivey <keith@stripped>
Smokefree DC
Washington, DC
linux timestampDotan Cohen27 Sep
  • Re: linux timestampFelix Geerinckx27 Sep
  • Re: linux timestampKeith Ivey27 Sep
  • Re: linux timestampNigel Wood27 Sep
    • Re: linux timestampDotan Cohen27 Sep