scadalts

How get values from database scadalts from last day


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.


Solution

  • 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