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)
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
http://lyricslist.com/lyrics/artist_albums/109/carlisle_belinda.php
Carlisle, Belinda Song Lyrics
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
