I currently have some cleaned data with new columns and formatted correctly, however the clause regarding from_unixtime is stumping me. I have tried the following:
from_unixtime(`time@timestamp`, '%H:%i:%s') AS Date_12am,
Is the main statement that I'm trying to get to display '00:03:02' where the time is starting at midnight for a 24hr period. I'm in the eastern timezone so I first tried recommended shell commands and verified that my MySQL workbench and config files were also the correct time. I tried setting the above clause to local time, 'SET AS', 'CONVERT', and playing around with the format to see if I could get the right combination. Nothing is seeming to work, so any help in this matter will be greatly appreciated!
SELECT
from_unixtime(`time@timestamp`),
data_format_0, data_format_1, data_format_2, data_format_3
FROM my_table_name;
/*The above is for checking to see if data is populated.*/
ALTER TABLE my_table_name
CHANGE data_format_0 OneMinuteAverage DOUBLE,
CHANGE data_format_1 ProductName VARCHAR(250),
CHANGE data_format_2 measruement1 DOUBLE,
CHANGE data_format_3 measurement2 DOUBLE;
SELECT
from_unixtime(`time@timestamp`, '%Y_%m_%d') AS `Date`,
from_unixtime(`time@timestamp`, '%H:%i:%s') AS Date_12am,
ROUND(OneMinuteAverage, 2) AS OneMinuteAverage,
ProductName, measurement1, measurement2
FROM my_table_name;
show variables like '%time_zone';
say?time@timestamp
column? can you show some sample data?time@timestamp
column is a REAL32 variable coming from easybuilder pro SQL query. @tadman and @Barmar my 'time_zone' value is 'SYSTEM' and my 'SYSTEM_TIME_ZONE' is Eastern Standard Time (my current timezone).