On 9/27/05, Nigel Wood <[EMAIL PROTECTED]> wrote:
> 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;
>
Thank you everyone. I'll set up the day and hour fields. It is a big
database, and I will be accessing often. I especially appreciated the
link to the proper chapter in the manual, as I am not as familiar with
the mysql manual as I probably should be.
Thanks.
Dotan Cohen
http://lyricslist.com/lyrics/artist_albums/389/osbourne_ozzy.php
Osbourne, Ozzy Song Lyrics
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
