[SOLVED] Converting Epoch to day of the week in new column in MySQL


I have a table with a column containing unix time. I wish to create a new column that contains the day of the week for this time. For example, 1436160600 would be a Monday in this column.

I have created a new column, entitled “day_of_week”

alter table master add column test varchar(20);

I now wish to update this new column with the appropriate values.

I found the MySQL Unixtimestamp() function (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp)

I then attempted the following

update master set day_of_week = _sent_time_stamp(from_unixtime(unix_timestamp, %W));

where _sent_time_stamp is the column containing the Unix time values

But this results in an Error 1064.

Can anyone advise?


Solution. Convert epoch to date time

alter table master add column test_date datetime ;

update master set test_date = from_unixtime(_sent_time_stamp) ;

convert datetime to day of week using dayname function

alter table master add column test_day varchar(20) ;

update master set test_day = dayname(test_date) ;

Answered By – LearningSlowly

Answer Checked By – Senaida (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.