---> Reply is under the quoted text <---
=>From: Michael T. Babcock [mailto:[EMAIL PROTECTED]]
=>Subject: SQL Error?
=>
=>I have a table that contains hourly work done on projects. It has
=>a basic structure of (extraneous fields left out):
=>
=>+------------+------------------+------+
=>| Field | Type | Null |
=>+------------+------------------+------+
=>| ID | int(10) unsigned | |
=>| ProjectID | bigint(20) | |
=>| ContactID | int(10) unsigned | YES |
=>| NotesID | int(10) unsigned | YES |
=>| Start_Time | datetime | |
=>| End_Time | datetime | YES |
=>+------------+------------------+------+
=>
=>I had made 4 entries for a day that had the wrong date;
=>eg "2001-07-01 9:00" instead of "2001-08-01 9:00".
=>
=>I did a seconds count between the incorrect times and the
=>correct times (2592000) and then ran:
=>
=>UPDATE ProjectTime set Start_Time=UNIX_TIMESTAMP(Start_Time)+2592000;
=>
=>This gave me warnings and thats when I realised that I hadn't
=>put a WHERE clause in, so I ran the same query in reverse (-2592000).
=>
=>Now all of my entries (in the entire table) have '0' for their
=>Start_Time.
=>
=>1) Is there any way (besides a restore from a 24hr old
=>backup tape) to get the old values back?
=>2) Why did these queries fail to do what I'd expected?
Michael,
As far as I know, there'll be no other solution than recovering from backup.
However, there's another "Caveat" here!
It seems to me to problems arise in your first query:
UNIX_TIMESTAMP(Start_Time)=2592000 will result in a large number, i.e.:
+-------------------------------+
| UNIX_TIMESTAMP(NOW())+2592000 |
+-------------------------------+
| 999888107 |
+-------------------------------+
If you insert this value into a datetime column, as you've done, you're
inserting an incompatible value:
+----+----------------+
| id | changed |
+----+----------------+
| 10 | 20000912112238 |
+----+----------------+
1 row in set (0.00 sec)
mysql> update link set changed='999888107' where value=36000;
Query OK, 1 rows affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> select * from link;
+----+----------------+
| id | changed |
+----+----------------+
| 10 | 00000000000000 |
+----+----------------+
Seems to me you'd have to convert your unixtime back to a timestamp before
inserting it:
UPDATE ProjectTime set
Start_Time=FROM_UNIXTIME(UNIX_TIMESTAMP(Start_Time)+2592000);
This should help you avoiding all zero values as a result next time.
Regards,
Sebastiaan J.A. Kamp
General Manager
VOF Post Zero
Hoogstraat 98a
3011 PT Rotterdam
tel. +31 1 04199140
fax. +31 1 04111335
@to: [EMAIL PROTECTED]
Information & Automation Erasmus University Rotterdam
Network & system manager, Lector "New Media"
Room L5-040, Woudestein
Burgemeester Oudlaan 50
3062 PA Rotterdam
tel. 010-4088638
@to: [EMAIL PROTECTED]
Executive Officer
Safenet BV
van Galenstraat 35
2518 EN Den Haag
tel. 070-3642968
fax. 070-3454537
@to: [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
