List:General Discussion« Previous MessageNext Message »
From:Nigel Wood Date:September 27 2005 5:34pm
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)
>SELECT * from listings WHERE timestamp(14:00 <= time <= 15:00)

Fair warning: Because MySQL won't  be able to make proper use of it's 
indexes the following queries will be VERY slow with any reasonable 
sized data set. If your going to be performing these queries often I'd 
recommend either storing the field as a datatime (you can do date time 
to unixtime conversion in MySQL using the unix_timestamp() function) or 
denormalising the data and storing both.

SELECT * from listings WHERE date_format('%W',from_unixstamp(timestamp)) = 'Wednesday';

SELECT * from listings WHERE cast( date_format('%H',from_unixstamp(timestamp)) as
unsigned) between 14 and 15;

>Of course, I don't expect these examples to work, I'm just trying to
>illustrate what I'm trying to accomplish. Until now, I have been
>pulling all the fields and checking the timestamp with php. But I
>believe that there must be a better way. Thanks.
>Dotan Cohen
>Carlisle, Belinda Song Lyrics

linux timestampDotan Cohen27 Sep
  • Re: linux timestampFelix Geerinckx27 Sep
  • Re: linux timestampKeith Ivey27 Sep
  • Re: linux timestampNigel Wood27 Sep
    • Re: linux timestampDotan Cohen27 Sep