Archived Forum

Hi everyone, sorry if this is not the right place to ask, please let me know.

I am wondering if anyone could clear my head on how data should be stored, processed and viewed when dealing with sampling based on unixtime stamps.

The situation is I am saving each energy reading with a column containing the unixtimestamps.

My hosting server is located somewhere in USA, so time difference is around 8 or 9 hours

My location is Israel, so I am +2.

I am saving information in a table named "data" like this

id         unix_time                               datetime                        energy(w)

1          1391424496        2014-02-03 12:48:40                     145

2        1391424506          2014-02-03 12:48:50                      150

etc etc

I am doing an INSERT with time() in the query so that sets the unix_time correctly.

When dealing with graphs visualization using fplot,

I belive I am doing it right by setting "localTimezone: true," in the chart.

Now when trying to process the data and store it in a "resume" table, things get difficut.

Once very hour, a php runs some code to generate or update values of totals for each day. I want to set a query that will group by days. However, I cant figure out how to define the START and END of the day times.

I guess I will need to only focus on the location of the device to know the start and end of day?

I tried this... (where "B" is the unixtimestamp)

$table_query =
    "SELECT
    B,
    FROM_UNIXTIME((B),'%Y-%m-%d') as date_formatted,
    COUNT( B ) AS num_reg_day,
    MIN(B) AS MINB,
    MAX(B) AS MAXB,
    MAX( Apparent_power ) AS consumo_maximo,
    MIN( Apparent_power ) AS consumo_minimo,
    SUM( Apparent_power ) AS lasuma,
    AVG( Apparent_power ) AS lamedia,
    AVG(temp_amb) AS meantemperature,
    MIN(temp_amb) AS min_temperature,
    MAX(temp_amb) AS max_temperature
    FROM `data`
    WHERE B > $unix_time_start
    GROUP BY date_formatted
    ORDER BY B DESC
    ";

Could someone help me a little bit? I am very lost.

Thank you very much