I'm trying to convert the datetime field in crashe...
# macosa
asparamancer
10/07/2020, 3:42 AMI'm trying to convert the datetime field in crashes to epoch and failing, eg it's "2020-10-07 033145.490 +0100" and I can't seem to get it to convert tin datetime/strftime. Any suggestions/solutions would be great
s
seph
10/07/2020, 2:21 PM
I see a hacky route.
seph
10/07/2020, 2:22 PM
The column is coming in as
2020-09-11 21:53:01.379 -0400seph
10/07/2020, 2:22 PM
sqlite expects
2020-09-11 21:53:01.379 -04:00seph
10/07/2020, 2:22 PM
So you’d need to string manipulate the timesonze into the expected format.
seph
10/07/2020, 2:22 PM
And then
SELECT strftime('%s','2020-09-11 21:53:01.379 -04:00');seph
10/07/2020, 2:23 PM
I might be reasonable to add a column with the datatime in unix time directly. Not totally sure what I think
seph
10/07/2020, 2:26 PM
Copy code
osquery> select datetime, strftime('%s', regex_match(datetime, "(.*)(..$)", 1) || ":" || regex_match(datetime, "(.*)(..$)", 2)) from crashes;
+-------------------------------+--------------------------------------------------------------------------------------------------------+
| datetime | strftime('%s', regex_match(datetime, "(.*)(..$)", 1) || ":" || regex_match(datetime, "(.*)(..$)", 2)) |
+-------------------------------+--------------------------------------------------------------------------------------------------------+
| 2020-09-11 21:53:01.379 -0400 | 1599875581 |
| 2020-09-21 10:25:06.941 -0400 | 1600698306 |
| 2020-09-21 10:31:08.939 -0400 | 1600698668 |
| 2020-09-21 10:31:38.282 -0400 | 1600698698 |
| 2020-09-21 10:25:06.941 -0400 | 1600698306 |
| 2020-09-21 10:31:08.924 -0400 | 1600698668 |
| 2020-09-21 10:31:38.283 -0400 | 1600698698 |
| 2020-09-09 19:02:58.401 -0400 | 1599692578 |
| 2020-09-07 23:14:46.617 -0400 | 1599534886 |
| 2020-09-10 01:44:26.389 -0400 | 1599716666 |
| 2020-09-10 01:49:23.995 -0400 | 1599716963 |
| 2020-09-10 01:48:29.837 -0400 | 1599716909 |
| 2020-09-10 01:52:54.630 -0400 | 1599717174 |
| 2020-09-10 01:53:10.321 -0400 | 1599717190 |
| 2020-09-10 01:53:16.149 -0400 | 1599717196 |
| 2020-09-12 20:02:17.686 -0400 | 1599955337 |
| 2020-09-12 20:03:35.544 -0400 | 1599955415 |
| 2020-09-12 20:03:30.305 -0400 | 1599955410 |
| 2020-09-12 20:03:38.242 -0400 | 1599955418 |
| 2020-09-08 20:54:25.848 -0400 | 1599612865 |
| 2020-09-08 20:54:21.497 -0400 | 1599612861 |
| 2020-09-19 11:47:37.534 -0400 | 1600530457 |
| 2020-09-21 12:09:17.646 -0400 | 1600704557 |
+-------------------------------+--------------------------------------------------------------------------------------------------------+f
fritz
10/07/2020, 2:50 PMAn alternative path is:
Copy code
SELECT strftime('%s', (SUBSTR(datetime, 0, 24) || SUBSTR(datetime, 25, 3) || ':' || SUBSTR(datetime,28,2)) ) AS epoch FROM users CROSS JOIN crashes USING(uid);✅ 1
fritz
10/07/2020, 2:51 PMThe important thing to remember @asparamancer is that you must join against users or you will be dropping data
s
seph
10/07/2020, 3:48 PM
I think substr is a little cleaner than regex.
seph
10/07/2020, 3:48 PM
(since these are fixed length strings)
a
asparamancer
10/09/2020, 11:50 AMjust saw this, will take a look - thank you both!
4 Views