MySQL ODBC Connector - linux timestamp

Author

linux timestamp

Dotan Cohen

2005-09-27, 11:23 am

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=3D=3Dw
ednesday)
or
SELECT * from listings WHERE timestamp(14:00 <=3D time <=3D 15:00)

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...sle_belinda.php
Carlisle, Belinda Song Lyrics

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Felix Geerinckx

2005-09-27, 1:23 pm

On 27/09/2005, 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.

SET @uts := UNIX_TIMESTAMP(NOW()
);

SELECT
IF(WEEKDAY(FROM_UNIX
TIME(@uts)) = 2, 'Yes', 'No') AS 'Wednesday?';

SELECT
IF(TIME(FROM_UNIXTIM
E(@uts)) BETWEEN '14:00' AND '15:00', 'Yes', 'No')
AS 'Between 2 and 3 pm?';

See http://dev.mysql.com/doc/mysql/en/d...-functions.html

--
felix

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Keith Ivey

2005-09-27, 1:23 pm

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==wedne
sday)
> 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_UNI
XTIME(your_timestamp
),'%W')) = 'Wednesday';

or

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

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

--
Keith Ivey <keith@smokefreedc.org>
Smokefree DC
http://www.smokefreedc.org
Washington, DC

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Nigel Wood

2005-09-27, 1:23 pm

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==wedne
sday)
>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',fro
m_unixstamp(timestam
p)) = 'Wednesday';

SELECT * from listings WHERE cast( date_format('%H',fro
m_unixstamp(timestam
p)) 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...sle_belinda.php
>Carlisle, Belinda Song Lyrics
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Dotan Cohen

2005-09-27, 8:24 pm

On 9/27/05, Nigel Wood <nwood@plus.net> wrote:
> Dotan Cohen wrote:
>
>
> 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',fro
m_unixstamp(timestam
p)) =
=3D 'Wednesday';
>
> SELECT * from listings WHERE cast( date_format('%H',fro
m_unixstamp(timest=[
/color]
amp)) as unsigned) between 14 and 15;[color=darkred]
>

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...bourne_ozzy.php
Osbourne, Ozzy Song Lyrics

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links