List:General Discussion« Previous MessageNext Message »
From:Dotan Cohen Date:September 27 2005 9:45pm
Subject:Re: linux timestamp
View as plain text  
On 9/27/05, Nigel Wood <nwood@stripped> 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.


Dotan Cohen
Osbourne, Ozzy 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