Issue
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
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)