Dear Michael,
> put a WHERE clause in, so I ran the same query in reverse (-2592000).
> 1) Is there any way (besides a restore from a 24hr old
> backup tape) to get the old values back?
I don't think so. Maybe someone has a trick for this. I'd be interested.
> 2) Why did these queries fail to do what I'd expected?
Look what the manual (http://www.mysql.com/doc/D/A/DATETIME.html) says
about this:
"Always specify year, month, and day, even if your column types are
TIMESTAMP(4) or TIMESTAMP(2). Otherwise, the value will not be a legal
date and 0 will be stored. "
And how do you specify year / month / day? Well, like this: YYYYMMDD or
YYMMDD (if it is specified as an integer). So, 25920000 is interpreted
as day 00 of month 20 of year 2592, which is an illegal date because the
month part makes no sense in this context. What does the manual say
about illegal dates?
"Illegal DATETIME, DATE, or TIMESTAMP values are converted to the
``zero'' value of the appropriate type ('0000-00-00 00:00:00',
'0000-00-00', or 00000000000000). "
Regards,
--
Stefan Hinz
Geschäftsführer / CEO iConnect e-commerce solutions GmbH
# www.js-webShop.com www.iConnect.de
# Gustav-Meyer-Allee 25, 13355 Berlin
# Tel: +49-30-46307-382 Fax: +49-30-46307-388
----- Original Message -----
From: "Michael T. Babcock" <[EMAIL PROTECTED]>
To: "MySQL List" <[EMAIL PROTECTED]>
Sent: Wednesday, August 08, 2001 4:53 PM
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?
> --
> Mike Babcock
>
>
>
> ---------------------------------------------------------------------
> 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
>
---------------------------------------------------------------------
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
