I'm doing a INSERT...SELECT and one column in the source table is a
datetime field with the target column being a timestamp format. I'm
changing the format because I can easily switch between timezones with
the latter rather than the former. However, instead of copying the
value from the source column to the target column it inserts the
current date and time. There are 13 million rows but no warnings or
errors are issued.

I've tried it with a small experiment, see below, and it works as
expected. Ideas/solutions welcome.


MySQL> create table tmp (DateTime datetime);
Query OK, 0 rows affected (0.00 sec)

MySQL> create table tmpa (DateTime timestamp);
Query OK, 0 rows affected (0.02 sec)

MySQL> insert into tmp values ("2007-01-01 00:00:00");
Query OK, 1 row affected (0.00 sec)

MySQL> insert into tmp values ("2007-12-25 00:00:00");
Query OK, 1 row affected (0.00 sec)

MySQL> insert into tmp values ("2007-12-26 00:00:00");
Query OK, 1 row affected (0.00 sec)

MySQL> insert into tmp values ("2007-04-01 00:00:00");
Query OK, 1 row affected (0.00 sec)

MySQL> insert into tmp values ("2007-11-05 00:00:00");
Query OK, 1 row affected (0.00 sec)

MySQL> select * from tmp;
+---------------------+
| DateTime |
+---------------------+
| 2007-01-01 00:00:00 |
| 2007-12-25 00:00:00 |
| 2007-12-26 00:00:00 |
| 2007-04-01 00:00:00 |
| 2007-11-05 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)

MySQL> insert into tmpa select * from tmp;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

MySQL> select * from tmpa;
+---------------------+
| DateTime |
+---------------------+
| 2007-01-01 00:00:00 |
| 2007-12-25 00:00:00 |
| 2007-12-26 00:00:00 |
| 2007-04-01 00:00:00 |
| 2007-11-05 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)

MySQL> Aborted

On 24 Aug, 10:54, Ciaran Byrne <ciaran.by...@ciaranbyrne.com> wrote:
> I'm doing a INSERT...SELECT and one column in the source table is a
> datetime field with the target column being a timestamp format. I'm
> changing the format because I can easily switch between timezones with
> the latter rather than the former. However, instead of copying the
> value from the source column to the target column it inserts the
> current date and time. There are 13 million rows but no warnings or
> errors are issued.
>
> I've tried it with a small experiment, see below, and it works as
> expected. Ideas/solutions welcome.
>
> MySQL> create table tmp (DateTime datetime);
> Query OK, 0 rows affected (0.00 sec)
>
> MySQL> create table tmpa (DateTime timestamp);
> Query OK, 0 rows affected (0.02 sec)
>
> MySQL> insert into tmp values ("2007-01-01 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> insert into tmp values ("2007-12-25 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> insert into tmp values ("2007-12-26 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> insert into tmp values ("2007-04-01 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> insert into tmp values ("2007-11-05 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> select * from tmp;
> +---------------------+
> | DateTime |
> +---------------------+
> | 2007-01-01 00:00:00 |
> | 2007-12-25 00:00:00 |
> | 2007-12-26 00:00:00 |
> | 2007-04-01 00:00:00 |
> | 2007-11-05 00:00:00 |
> +---------------------+
> 5 rows in set (0.00 sec)
>
> MySQL> insert into tmpa select * from tmp;
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> MySQL> select * from tmpa;
> +---------------------+
> | DateTime |
> +---------------------+
> | 2007-01-01 00:00:00 |
> | 2007-12-25 00:00:00 |
> | 2007-12-26 00:00:00 |
> | 2007-04-01 00:00:00 |
> | 2007-11-05 00:00:00 |
> +---------------------+
> 5 rows in set (0.00 sec)
>
> MySQL> Aborted

Your output shows the copies as successful as opposed to having the
current date/time as you say?

How does having the fieldas timestamp rather than datetime help you
with timezones?

On Aug 24, 11:11 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 24 Aug, 10:54, Ciaran Byrne <ciaran.by...@ciaranbyrne.com> wrote:
>
>
>
> > I'm doing a INSERT...SELECT and one column in the source table is a
> > datetime field with the target column being a timestamp format. I'm
> > changing the format because I can easily switch between timezones with
> > the latter rather than the former. However, instead of copying the
> > value from the source column to the target column it inserts the
> > current date and time. There are 13 million rows but no warnings or
> > errors are issued.
>
> > I've tried it with a small experiment, see below, and it works as
> > expected. Ideas/solutions welcome.
>
> > MySQL> create table tmp (DateTime datetime);
> > Query OK, 0 rows affected (0.00 sec)
>
> > MySQL> create table tmpa (DateTime timestamp);
> > Query OK, 0 rows affected (0.02 sec)
>
> > MySQL> insert into tmp values ("2007-01-01 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> insert into tmp values ("2007-12-25 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> insert into tmp values ("2007-12-26 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> insert into tmp values ("2007-04-01 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> insert into tmp values ("2007-11-05 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> select * from tmp;
> > +---------------------+
> > | DateTime |
> > +---------------------+
> > | 2007-01-01 00:00:00 |
> > | 2007-12-25 00:00:00 |
> > | 2007-12-26 00:00:00 |
> > | 2007-04-01 00:00:00 |
> > | 2007-11-05 00:00:00 |
> > +---------------------+
> > 5 rows in set (0.00 sec)
>
> > MySQL> insert into tmpa select * from tmp;
> > Query OK, 5 rows affected (0.00 sec)
> > Records: 5 Duplicates: 0 Warnings: 0
>
> > MySQL> select * from tmpa;
> > +---------------------+
> > | DateTime |
> > +---------------------+
> > | 2007-01-01 00:00:00 |
> > | 2007-12-25 00:00:00 |
> > | 2007-12-26 00:00:00 |
> > | 2007-04-01 00:00:00 |
> > | 2007-11-05 00:00:00 |
> > +---------------------+
> > 5 rows in set (0.00 sec)
>
> > MySQL> Aborted
>
> Your output shows the copies as successful as opposed to having the
> current date/time as you say?

I should have been clearer. The example I gave does work, but the real
statement does not.

>
> How does having the fieldas timestamp rather than datetime help you
> with timezones?

The database always uses UTC so if I run a report based on the date/
time now I'll be one hour out, but if I execute SET TIMEZONE = GB;
then my timezone will become British Summer Time and I can look at
records that occurred at my clock time rather than the stored UTC
time.

HTH

On 24 Aug, 12:39, Ciaran Byrne <ciaran.by...@ciaranbyrne.com> wrote:
> > Your output shows the copies as successful as opposed to having the
> > current date/time as you say?
>
> I should have been clearer. The example I gave does work, but the real
> statement does not.

So you show us something that works and ask us to tell you why
something that you haven't shown us doesn't work!

Well that's clear.

How the H*LL do you expect anyone to help you with that!!!

Hint, maybe posting the stuff that doesn't work might be useful.

Ciaran Byrne wrote:
> I'm doing a INSERT...SELECT and one column in the source table is a
> datetime field with the target column being a timestamp format. I'm
> changing the format because I can easily switch between timezones with
> the latter rather than the former. However, instead of copying the
> value from the source column to the target column it inserts the
> current date and time. There are 13 million rows but no warnings or
> errors are issued.
>
> I've tried it with a small experiment, see below, and it works as
> expected. Ideas/solutions welcome.
>
>
> MySQL> create table tmp (DateTime datetime);
> Query OK, 0 rows affected (0.00 sec)
>
> MySQL> create table tmpa (DateTime timestamp);
> Query OK, 0 rows affected (0.02 sec)
>
> MySQL> insert into tmp values ("2007-01-01 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> insert into tmp values ("2007-12-25 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> insert into tmp values ("2007-12-26 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> insert into tmp values ("2007-04-01 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> insert into tmp values ("2007-11-05 00:00:00");
> Query OK, 1 row affected (0.00 sec)
>
> MySQL> select * from tmp;
> +---------------------+
> | DateTime |
> +---------------------+
> | 2007-01-01 00:00:00 |
> | 2007-12-25 00:00:00 |
> | 2007-12-26 00:00:00 |
> | 2007-04-01 00:00:00 |
> | 2007-11-05 00:00:00 |
> +---------------------+
> 5 rows in set (0.00 sec)
>
> MySQL> insert into tmpa select * from tmp;
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> MySQL> select * from tmpa;
> +---------------------+
> | DateTime |
> +---------------------+
> | 2007-01-01 00:00:00 |
> | 2007-12-25 00:00:00 |
> | 2007-12-26 00:00:00 |
> | 2007-04-01 00:00:00 |
> | 2007-11-05 00:00:00 |
> +---------------------+
> 5 rows in set (0.00 sec)
>
> MySQL> Aborted
>

OK, this works as it should. How about showing us the failing tables'
layout and the failing code?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Aug 24, 12:51 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Ciaran Byrne wrote:
> > I'm doing a INSERT...SELECT and one column in the source table is a
> > datetime field with the target column being a timestamp format. I'm
> > changing the format because I can easily switch between timezones with
> > the latter rather than the former. However, instead of copying the
> > value from the source column to the target column it inserts the
> > current date and time. There are 13 million rows but no warnings or
> > errors are issued.
>
> > I've tried it with a small experiment, see below, and it works as
> > expected. Ideas/solutions welcome.
>
> > MySQL> create table tmp (DateTime datetime);
> > Query OK, 0 rows affected (0.00 sec)
>
> > MySQL> create table tmpa (DateTime timestamp);
> > Query OK, 0 rows affected (0.02 sec)
>
> > MySQL> insert into tmp values ("2007-01-01 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> insert into tmp values ("2007-12-25 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> insert into tmp values ("2007-12-26 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> insert into tmp values ("2007-04-01 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> insert into tmp values ("2007-11-05 00:00:00");
> > Query OK, 1 row affected (0.00 sec)
>
> > MySQL> select * from tmp;
> > +---------------------+
> > | DateTime |
> > +---------------------+
> > | 2007-01-01 00:00:00 |
> > | 2007-12-25 00:00:00 |
> > | 2007-12-26 00:00:00 |
> > | 2007-04-01 00:00:00 |
> > | 2007-11-05 00:00:00 |
> > +---------------------+
> > 5 rows in set (0.00 sec)
>
> > MySQL> insert into tmpa select * from tmp;
> > Query OK, 5 rows affected (0.00 sec)
> > Records: 5 Duplicates: 0 Warnings: 0
>
> > MySQL> select * from tmpa;
> > +---------------------+
> > | DateTime |
> > +---------------------+
> > | 2007-01-01 00:00:00 |
> > | 2007-12-25 00:00:00 |
> > | 2007-12-26 00:00:00 |
> > | 2007-04-01 00:00:00 |
> > | 2007-11-05 00:00:00 |
> > +---------------------+
> > 5 rows in set (0.00 sec)
>
> > MySQL> Aborted
>
> OK, this works as it should. How about showing us the failing tables'
> layout and the failing code?
>

I discovered the reason: I was using the table definition from an
earlier mysqldump which changed the definition of the column to be
"DateTime timestamp default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP". Removing the default and on update has fixed the
problem.

Thanks for the replies people.

> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================