Poor choice at db creation


OK i've been ignoring brexit and the fact our politicians were granted a delay and promptly went on holiday….

So its been a little journey into utilising the data i've recorded for the last 9 months in an sqlite db and its at this point i realise that perhaps whilst my data is readable to me without parsing i should have stored dates/times in a different format.

Currently date has its own column and time its own, when i created this it seemed to make sense in that without much work i should be able to pull out yesterdays data purely based on one column, but now i think it was the wrong thing to do. Currently an extract of data using pandas looks like;

       IDX        DATE      TIME  EXTERNAL  FRONTROOM  BEDROOM  KITCHEN
149  69773  2019-04-20  19:11:11    19.410      19.09    21.00    21.27
148  69774  2019-04-20  19:11:21    19.410      19.09    21.09    21.27
147  69775  2019-04-20  19:23:09    19.410      19.09    21.09    21.27
146  69776  2019-04-20  19:26:13    18.910      19.09    21.09    21.27
145  69777  2019-04-20  19:27:49    18.910      19.09    21.09    21.27
144  69778  2019-04-20  19:28:23    18.910      19.09    21.09    21.27
143  69779  2019-04-20  19:41:14    18.280      19.09    21.09    21.27
142  69780  2019-04-20  19:41:19    18.280      19.09    21.14    21.27
141  69781  2019-04-20  19:53:09    18.280      19.09    21.14    21.18
140  69782  2019-04-20  19:56:16    17.640      19.09    21.14    21.18

but after a bit of extraction, a bit of manipulation that proved more awkward than i anticipated a bit of clarity dawned and if i had possibly more thought than i applied at the start i reckon date & time should have been in epoch format…

| EPOCH  | 1555857826                                                      |
| NORMAL | GMT: Sunday, April 21, 2019 2:43:46 PM                          |
|        | Your time zone: Sunday, April 21, 2019 3:43:46 PM GMT+01:00 DST |

See also