update and times

hi all...

i'm doing tests with a table that gets updated based on random unix
times it contains. there is a column that has a bunch or random times
that look like:

+---------------------+-------------------+
| date_time | unix_time |
+---------------------+-------------------+
| 2010-10-01 10:24:52 | 12859430921341418 |
| 2010-10-01 21:18:13 | 12859822937839442 |
| 2010-10-01 16:08:00 | 12859636809115039 |
| 2010-10-01 19:47:43 | 12859768633824661 |
| 2010-10-01 16:48:30 | 12859661104829142 |
| 2010-10-01 15:25:37 | 12859611374324533 |
| 2010-10-01 12:27:28 | 12859504483288358 |
+---------------------+-------------------+


what i'm trying to do is update the column only of one of those times
isn't yet passed. and it works. except sometimes...

like these 2 unix times:

this was in the table under unix time: 12862162385941345...

this 12862162510269684 got passed in the update command as in:

update the_table set updated = 1 where unix_time < 12862162510269684
limit 1;

executing this query didn't update the record.

why?


thanks...



--
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: update and times

Those unix_time values don't seem to correspond to the dates you have.

select NOW(), UNIX_TIMESTAMP(NOW());
+---------------------+-----------------------+
| NOW() | UNIX_TIMESTAMP(NOW()) |
+---------------------+-----------------------+
| 2010-10-04 13:18:08 | 1286223488 |
+---------------------+-----------------------+

1286223428 vs 12862162510269684

Your value has far too many digits. That's also beyond the range of a 32 bi=
t int. Are you using BIGINT, or VARCHAR?

I suspect the issues is due because of something different about the values=
you have in your table. Try posting the SHOW CREATE TABLE table \G output=
, and a sample INSERT statement to populate the table. That way someone ca=
n try to reproduce the behavior you're seeing.




-----Original Message-----
From: kalin m [mailto:kalin@el.net]
Sent: Monday, October 04, 2010 1:11 PM
To: [MySQL]
Subject: update and times



hi all...

i'm doing tests with a table that gets updated based on random unix
times it contains. there is a column that has a bunch or random times
that look like:

+---------------------+-------------------+
| date_time | unix_time |
+---------------------+-------------------+
| 2010-10-01 10:24:52 | 12859430921341418 |
| 2010-10-01 21:18:13 | 12859822937839442 |
| 2010-10-01 16:08:00 | 12859636809115039 |
| 2010-10-01 19:47:43 | 12859768633824661 |
| 2010-10-01 16:48:30 | 12859661104829142 |
| 2010-10-01 15:25:37 | 12859611374324533 |
| 2010-10-01 12:27:28 | 12859504483288358 |
+---------------------+-------------------+


what i'm trying to do is update the column only of one of those times
isn't yet passed. and it works. except sometimes...

like these 2 unix times:

this was in the table under unix time: 12862162385941345...

this 12862162510269684 got passed in the update command as in:

update the_table set updated =3D 1 where unix_time < 12862162510269684
limit 1;

executing this query didn't update the record.

why?


thanks...



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
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: update and times

right.... the unix times in the example table were just that - examples
from a few days ago...

the example with the query was a 'real one' something that happened today...

it's a 64 bit machine. the unix times are stored in a bigint column.
the times in the column and the update statement are the same length...

here is the information for that column/table:

+-----------+---------------------+------+-----+------------ -------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-----------+---------------------+------+-----+------------ -------+----------------+
| id | int(10) unsigned | NO | PRI | NULL |
auto_increment |
| normal_time | datetime | NO | |
| |
| unix_time | bigint(20) unsigned | NO | UNI | 0
| |
| updated | tinyint(1) | NO | | 0
| |
+-----------+---------------------+------+-----+------------ -------+----------------+


at the end what is compared is two long int values - 12862162510269684
and 12862162385941345 in the update query....




Gavin Towey wrote:
> Those unix_time values don't seem to correspond to the dates you have.
>
> select NOW(), UNIX_TIMESTAMP(NOW());
> +---------------------+-----------------------+
> | NOW() | UNIX_TIMESTAMP(NOW()) |
> +---------------------+-----------------------+
> | 2010-10-04 13:18:08 | 1286223488 |
> +---------------------+-----------------------+
>
> 1286223428 vs 12862162510269684
>
> Your value has far too many digits. That's also beyond the range of a 32 bit int. Are you using BIGINT, or VARCHAR?
>
> I suspect the issues is due because of something different about the values you have in your table. Try posting the SHOW CREATE TABLE table \G output, and a sample INSERT statement to populate the table. That way someone can try to reproduce the behavior you're seeing.
>
>
>
>
> -----Original Message-----
> From: kalin m [mailto:kalin@el.net]
> Sent: Monday, October 04, 2010 1:11 PM
> To: [MySQL]
> Subject: update and times
>
>
>
> hi all...
>
> i'm doing tests with a table that gets updated based on random unix
> times it contains. there is a column that has a bunch or random times
> that look like:
>
> +---------------------+-------------------+
> | date_time | unix_time |
> +---------------------+-------------------+
> | 2010-10-01 10:24:52 | 12859430921341418 |
> | 2010-10-01 21:18:13 | 12859822937839442 |
> | 2010-10-01 16:08:00 | 12859636809115039 |
> | 2010-10-01 19:47:43 | 12859768633824661 |
> | 2010-10-01 16:48:30 | 12859661104829142 |
> | 2010-10-01 15:25:37 | 12859611374324533 |
> | 2010-10-01 12:27:28 | 12859504483288358 |
> +---------------------+-------------------+
>
>
> what i'm trying to do is update the column only of one of those times
> isn't yet passed. and it works. except sometimes...
>
> like these 2 unix times:
>
> this was in the table under unix time: 12862162385941345...
>
> this 12862162510269684 got passed in the update command as in:
>
> update the_table set updated = 1 where unix_time < 12862162510269684
> limit 1;
>
> executing this query didn't update the record.
>
> why?
>
>
> thanks...
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=gtowey@ffn.com
>
>
> This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt
court, Sunnyvale, CA 94089, USA, FriendFinder.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: update and times

On Mon, 04 Oct 2010 16:11:08 -0400
kalin m wrote:

>

>
> what i'm trying to do is update the column only of one of those times
> isn't yet passed. and it works. except sometimes...
>
> like these 2 unix times:
>
> this was in the table under unix time: 12862162385941345...
>
> this 12862162510269684 got passed in the update command as in:
>
> update the_table set updated = 1 where unix_time < 12862162510269684
> limit 1;
>
> executing this query didn't update the record.
>
> why?
The two values you have here are equal:
sample data : 12862162510269684
query: where unix_time < 12862162510269684
and therefore the 'less than' query did not match that row.


--
Simcha Younger

--
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: update and times

--------------050609060309050103010603
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit



Simcha Younger wrote:
> On Mon, 04 Oct 2010 16:11:08 -0400
> kalin m wrote:
>
>
>
>
>> what i'm trying to do is update the column only of one of those times
>> isn't yet passed. and it works. except sometimes...
>>
>> like these 2 unix times:
>>
>> this was in the table under unix time: 12862162385941345...
>>
>> this 12862162510269684 got passed in the update command as in:
>>
>> update the_table set updated = 1 where unix_time < 12862162510269684
>> limit 1;
>>
>> executing this query didn't update the record.
>>
>> why?
>>
> The two values you have here are equal:
> sample data : 12862162510269684
> query: where unix_time < 12862162510269684
> and therefore the 'less than' query did not match that row.
>
>
sorry... not following.... the value in the table was
12862162385941345. the time in the query was 12862162510269684.

mysql> select 12862162385941345 < 12862162510269684;
+---------------------------------------+
| 12862162385941345 < 12862162510269684 |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)



--------------050609060309050103010603--

Re: update and times

On Wed, 06 Oct 2010 17:48:55 -0400
kalin m wrote:

>
>
> Simcha Younger wrote:

> >> executing this query didn't update the record.
> >>
> >> why?
> >>
> > The two values you have here are equal:
> > sample data : 12862162510269684
> > query: where unix_time < 12862162510269684
> > and therefore the 'less than' query did not match that row.
> >
> >
> sorry... not following.... the value in the table was
> 12862162385941345. the time in the query was 12862162510269684.

Sorry, I misread your question.

did you check before you ran the query that this is the only matching record for your condition?
It is possible that the limit 1 is preventing the update because you have more than one record less than the timestamp in the query.

You might also want to add to your where condition:
AND `updated` = 0; so it will skip rows which have already been updated.

--
Simcha Younger

--
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