Converting integers to datetime

I have the following fields in a database:

year integer(4) not null default '0'
month integer(2) not null default '0'
day integer(2) not null default '0'
hour integer(2) not null default '0'
minute integer(2) not null default '0'

and just added (using alter table) the following blank field:

date datetime not null default '0'

I want to use (year, month, day, hour, minute) to fill in the datetime field
using the MySQL monitor. How can this be done?

Thanks!
Pete

Peter wrote:
> I have the following fields in a database:
>
> year integer(4) not null default '0'
> month integer(2) not null default '0'
> day integer(2) not null default '0'
> hour integer(2) not null default '0'
> minute integer(2) not null default '0'
>
> and just added (using alter table) the following blank field:
>
> date datetime not null default '0'
>
> I want to use (year, month, day, hour, minute) to fill in the datetime field
> using the MySQL monitor. How can this be done?
>
> Thanks!
> Pete

update tablename
set `date` = concat(
lpad( `year`, 4,'0' ), '-',
lpad( `month`, 2,'0' ), '-',
lpad( `day`, 2,'0' ), ' ',
lpad( `hour`, 2,'0' ), ':',
lpad( `minute`, 2,'0' ), ':00'
);

Side note: Don't call your field 'date'. It's a reserved word.
Even though MySQL is forgiving, you can face subtle errors if you
are not extra careful.

ciao
gmax

--
_ _ _ _
(_|| | |(_|><
_|
http://gmax.oltrelinux.com

Giuseppe Maxia <gmax_@_cpan_._org> wrote:
> Peter wrote:
>> I have the following fields in a database:
>>
>> year integer(4) not null default '0'
>> month integer(2) not null default '0'
>> day integer(2) not null default '0'
>> hour integer(2) not null default '0'
>> minute integer(2) not null default '0'
>>
>> and just added (using alter table) the following blank field:
>>
>> date datetime not null default '0'
>>
>> I want to use (year, month, day, hour, minute) to fill in the datetime field
>> using the MySQL monitor. How can this be done?
>>
>> Thanks!
>> Pete
>
> update tablename
> set `date` = concat(
> lpad( `year`, 4,'0' ), '-',
> lpad( `month`, 2,'0' ), '-',
> lpad( `day`, 2,'0' ), ' ',
> lpad( `hour`, 2,'0' ), ':',
> lpad( `minute`, 2,'0' ), ':00'
> );
>
> Side note: Don't call your field 'date'. It's a reserved word.
> Even though MySQL is forgiving, you can face subtle errors if you
> are not extra careful.
>
> ciao
> gmax

Hmmm. Never heard of lpad. I'll go look that up now.

Point taken with the field name. You're absolutely right; I'll change it.

A big thanks for the reply! :)

Pete

Peter schrieb:
> I have the following fields in a database:
>
> year integer(4) not null default '0'
> month integer(2) not null default '0'
> day integer(2) not null default '0'
> hour integer(2) not null default '0'
> minute integer(2) not null default '0'
>
> and just added (using alter table) the following blank field:
>
> date datetime not null default '0'
>
> I want to use (year, month, day, hour, minute) to fill in the datetime field
> using the MySQL monitor. How can this be done?
>

By using the appropriate UPDATE statement, I'd guess. Like so (untested)

UPDATE table
SET date <----------- bad, bad choice for a name.
= concat(year,'-',month,'-',day,' ',hour,':',second)

You might want to read the documentation concerning the use of
reserved names, the date-time and string functions, and the format for
date/time literals. The documentation is available at dev.mysql.com/doc