Re: update and times
On Wed, 06 Oct 2010 17:48:55 -0400 kalin m ka...@el.net 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 sim...@syounger.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: update and times
Simcha Younger wrote: On Mon, 04 Oct 2010 16:11:08 -0400 kalin m ka...@el.net 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)
Re: update and times
On Mon, 04 Oct 2010 16:11:08 -0400 kalin m ka...@el.net 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 sim...@syounger.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.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 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:ka...@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=gto...@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=arch...@jab.org
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:ka...@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=gto...@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=arch...@jab.org
