I need to get data from DB scadalts from last day.
I have data in table pointValues where is column pointValue and ts but is not timestamp.
Column ts is type BIGINT(20)
Checking ts is unixtime
SELECT
pointValue,
ts,
from_unixtime(ts),
YEAR(from_unixtime(ts)),
MONTH(from_unixtime(ts)),
DAY(from_unixtime(ts))
FROM
pointValues;
The result null is wrong is not unixtime.
I don't know how to create condition where because - I don't know how to interpret value in column ts.
Column ts should be interpreted with greater accuracy.
eg:
SELECT
pointValue,
ts,
from_unixtime(ts/1000),
YEAR(from_unixtime(ts/1000)),
MONTH(from_unixtime(ts/1000)),
DAY(from_unixtime(ts/1000))
FROM
pointValues;
And we may get values from last day eg:
SELECT
pointValue,
ts,
YEAR(from_unixtime(ts/1000)),
MONTH(from_unixtime(ts/1000)),
DAY(from_unixtime(ts/1000))
FROM
pointValues
WHERE
YEAR(from_unixtime(ts/1000)) = YEAR(NOW() - INTERVAL 1 day) and
MONTH(from_unixtime(ts/1000)) = MONTH(NOW() - INTERVAL 1 day) and
DAY(from_unixtime(ts/1000)) = DAY(NOW() - INTERVAL 1 day)
Thanks
Maybe it will be useful also
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments