SELECT records less than 15 minutes old

Hi,

I'm trying to write a statement that will return all records =
that match a particular order_id and that have a timestamp within the =
last 15 minutes. I thought that this should work:

SELECT * FROM `records` WHERE `order_id` =3D $order_id AND (`time_stamp` =
>=3D DATE_SUB(NOW(), INTERVAL 15 MINUTE))

but it returns zero rows, no matter what. If I up the interval to =
something huge, like 15000, it will then return records. Very strange. =
It's almost like it's using seconds, not minutes.

Is my syntax wrong?

Thanks,
Marc=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: SELECT records less than 15 minutes old

--20cf307f35fad0ab4304a6148bde
Content-Type: text/plain; charset=ISO-8859-1

just a quick debug:

SELECT time_stamp,DATE_SUB(NOW(), INTERVAL 15 MINUTE) FROM `records` WHERE
`order_id` = $order_id order by time_stamp desc limit 10;

what do you get?



2011/6/19

> Hi,
>
> I'm trying to write a statement that will return all records that
> match a particular order_id and that have a timestamp within the last 15
> minutes. I thought that this should work:
>
> SELECT * FROM `records` WHERE `order_id` = $order_id AND (`time_stamp` >=
> DATE_SUB(NOW(), INTERVAL 15 MINUTE))
>
> but it returns zero rows, no matter what. If I up the interval to
> something huge, like 15000, it will then return records. Very strange.
> It's almost like it's using seconds, not minutes.
>
> Is my syntax wrong?
>
> Thanks,
> Marc
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>
>


--
Claudio

--20cf307f35fad0ab4304a6148bde--

Re: SELECT records less than 15 minutes old

On Jun 19, 2011, at 11:11 AM, Claudio Nanni wrote:

> just a quick debug:

Thanks, Claudio. It turned out to be that NOW() was using the =
server's time and my timestamp was based on my timezone. After fixing =
that, the SELECT statement works properly.

Marc=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: SELECT records less than 15 minutes old

Am 19.06.2011 21:06, schrieb sono-io@fannullone.us:
> On Jun 19, 2011, at 11:11 AM, Claudio Nanni wrote:
>
>> just a quick debug:
>
> Thanks, Claudio. It turned out to be that NOW() was using the server's time and my timestamp was based on my timezone. After fixing that, the SELECT statement works properly.
>
> Marc

You should use UTC time zone or you will run into trouble with DST.

re,
wh

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: SELECT records less than 15 minutes old

>-----Original Message-----
>From: walter harms [mailto:wharms@bfs.de]
>Sent: Monday, June 20, 2011 7:07 AM
>To: sono-io@fannullone.us
>Cc: mysql@lists.mysql.com
>Subject: Re: SELECT records less than 15 minutes old
>
>
>
>Am 19.06.2011 21:06, schrieb sono-io@fannullone.us:
>> On Jun 19, 2011, at 11:11 AM, Claudio Nanni wrote:
>>
>>> just a quick debug:
>>
>> Thanks, Claudio. It turned out to be that NOW() was using the server's
>time and my timestamp was based on my timezone. After fixing that, the
>SELECT
>statement works properly.
>>
>> Marc
>
>You should use UTC time zone or you will run into trouble with DST.
>
[JS] If you do that, you can't use an automatic timestamp field. You have to
set the field yourself.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SELECT records less than 15 minutes old

On Jun 20, 2011, at 10:11 AM, Jerry Schwartz wrote:

>> You should use UTC time zone or you will run into trouble with DST.
>>=20
> [JS] If you do that, you can't use an automatic timestamp field. You =
have to=20
> set the field yourself.

Thanks Walter and Jerry.

Is there a way to get NOW() to use UTC instead of the server =
timezone? (The server is not mine, so I can't change the my.cnf.) =
Here's my statement:

SELECT * FROM `log` WHERE `id` =3D $_id AND ( `time_stamp` >=3D =
DATE_SUB(NOW(), INTERVAL 30 MINUTE) )

Earlier in my PHP script I've used date_default_timezone_set, =
but that doesn't affect the MySQL statement.

------

Possible Solution

I tried: SET time_zone =3D 'UTC';=20
but MySQL complained with: #1298 - Unknown or incorrect time zone: 'UTC'

I then tried:
SET time_zone =3D '-0:00';=20
and that seems to have worked. Is this the correct way to do it?

Thanks,
Marc=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: SELECT records less than 15 minutes old

from:http://dev.mysql.com/doc/refman/5.0/en/time-zone-suppor t.html
SET GLOBAL time_zone = timezone;

from:http://dev.mysql.com/doc/refman/5.0/en/date-and-time-fu nctions.html#function_utc-timestamp
* UTC_TIMESTAMP, UTC_TIMESTAMP()

I have the same results, according to the docs timezone is the offset to UTC (what is used internaly of cause).
see also: http://www.mysqlfaqs.net/mysql-faqs/General-Questions/How-to -manage-Time-Zone-in-MySQL

btw: please notice the difference between:

mysql> select @@session.time_zone ;
+---------------------+
| @@session.time_zone |
+---------------------+
| +00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@global.time_zone ;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM |
+--------------------+
1 row in set (0.00 sec)



ntl personally i would say it should work with "UTC" but someone decided otherwise.

(i just found a hint why: http://dev.mysql.com/doc/refman/4.1/en/mysql-tzinfo-to-sql.h tml )


hope that helps,
wh

Am 21.06.2011 00:00, schrieb sono-io@fannullone.us:
> On Jun 20, 2011, at 10:11 AM, Jerry Schwartz wrote:
>
>>> You should use UTC time zone or you will run into trouble with DST.
>>>
>> [JS] If you do that, you can't use an automatic timestamp field. You have to
>> set the field yourself.
>
> Thanks Walter and Jerry.
>
> Is there a way to get NOW() to use UTC instead of the server timezone? (The server is not mine, so I can't change the my.cnf.) Here's my statement:
>
> SELECT * FROM `log` WHERE `id` = $_id AND ( `time_stamp` >= DATE_SUB(NOW(), INTERVAL 30 MINUTE) )
>
> Earlier in my PHP script I've used date_default_timezone_set, but that doesn't affect the MySQL statement.
>
> ------
>
> Possible Solution
>
> I tried: SET time_zone = 'UTC';
> but MySQL complained with: #1298 - Unknown or incorrect time zone: 'UTC'
>
> I then tried:
> SET time_zone = '-0:00';
> and that seems to have worked. Is this the correct way to do it?
>
> Thanks,
> Marc

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: SELECT records less than 15 minutes old

>-----Original Message-----
>From: sono-io@fannullone.us [mailto:sono-io@fannullone.us]
>Sent: Monday, June 20, 2011 6:01 PM
>To: mysql@lists.mysql.com
>Cc: wharms@bfs.de; Jerry Schwartz
>Subject: Re: SELECT records less than 15 minutes old
>
>On Jun 20, 2011, at 10:11 AM, Jerry Schwartz wrote:
>
>>> You should use UTC time zone or you will run into trouble with DST.
>>>
>> [JS] If you do that, you can't use an automatic timestamp field. You have
>> to
>> set the field yourself.
>
> Thanks Walter and Jerry.
>
> Is there a way to get NOW() to use UTC instead of the server timezone?
>(The server is not mine, so I can't change the my.cnf.) Here's my statement:
>
>SELECT * FROM `log` WHERE `id` = $_id AND ( `time_stamp` >= DATE_SUB(NOW(),
>INTERVAL 30 MINUTE) )
>

> Earlier in my PHP script I've used date_default_timezone_set, but that
>doesn't affect the MySQL statement.
>
>------
>
>Possible Solution
>
>I tried: SET time_zone = 'UTC';
>but MySQL complained with: #1298 - Unknown or incorrect time zone: 'UTC'
>
>I then tried:
>SET time_zone = '-0:00';
>and that seems to have worked. Is this the correct way to do it?
>

[JS] I believe that is the only way to do it.

The reason you can't use time zone names is that the time zone tables in MySQL
have not been loaded.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org