Matthew Halliday wrote:
> I used the SQLite Studio to create the table so used the DATETIME data type
> for that, and although I used yyyy-mm-dd hh:mm:ss in the script

That is correct.

> it seems to have reverted it to dd/mm/yy hh:mm:ss.

That would not be usable.
Check the actual format with the sqlite3 command-line shell.


Assuming the timestamp format is usable, you can compute the differences
with a statement like this:

-- add three columns; then:
UPDATE MyTable
SET (diff_used, diff_free, diff_free_pc)
  = (SELECT MyTable.used_mb - ifnull(prev.used_mb, 0),
            MyTable.free_mb - ifnull(prev.free_mb, 0),
            MyTable.free_pc - ifnull(prev.free_pc, 0)
     FROM MyTable AS prev
     WHERE prev.date_time < MyTable.date_time
     ORDER BY date_time DESC
     LIMIT 1);

This is much faster if there is an index on date_time.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to