Poor Choice Made at DB Creation
OK i’ve been ignoring brexit and the fact our politicians were granted a delay and promptly went on holiday :rage: so in an effort to further distract from the failings of my government i took a little jaunt into utilising the data i’ve been recording for the last 9 months and its at this point i realise that perhaps whilst my data is readable to me i should have stored dates/times in a different format to allow for automated data analysis.
Currently the date and time are stored in their own field, when i created the DB 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;
text code snippet start
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.18text code snippet end
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…
bash code snippet start
| 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 |bash code snippet end