I have a table that has start_date and expire_date in the format: 2004-12-31
That is an output format... yes? If the column is a string type (char/varchar/text...), you should change it to a DATE type, or maybe TIMESTAMP. The default output format for DATE type columns is YYYY-MM-DD.
I also have a start field and expire field
I need to convert the start_date to Epoch seconds and place in start and convert the expire_date and place in expire.
Why? In general it is better to calculate things on the fly when you need it, and only store the information once. You should store either the date or the epoch value, and calculate the other when you need it.
I'm unsure how to convert and how to do this in one go via a query
A simple UPDATE will do it:
UPDATE table SET start = UNIX_TIMESTAMP(start_date), end = UNIX_TIMESTAMP(end_date);
# remove redundant columns ALTER TABLE table DROP start_date, DROP end_end;
# create columns on the fly
SELECT *,
FROM_UNIXTIME(start,"%Y-%m-%d") start_date,
FROM_UNIXTIME(end,"%Y-%m-%d") end_date
FROM table ...<URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_type_overview.html <URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html > <URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html >
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
