Title
#macos
a

asparamancer

10/07/2020, 3:42 AM
I'm trying to convert the datetime field in crashes to epoch and failing, eg it's "2020-10-07 03:31:45.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.
2:22 PM
The column is coming in as
2020-09-11 21:53:01.379 -0400
2:22 PM
sqlite expects
2020-09-11 21:53:01.379 -04:00
2:22 PM
So you’d need to string manipulate the timesonze into the expected format.
2:22 PM
And then
SELECT strftime('%s','2020-09-11 21:53:01.379 -04:00');
2:23 PM
I might be reasonable to add a column with the datatime in unix time directly. Not totally sure what I think
2:26 PM
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 PM
An alternative path is:
SELECT strftime('%s', (SUBSTR(datetime, 0, 24) || SUBSTR(datetime, 25, 3) || ':' || SUBSTR(datetime,28,2)) ) AS epoch FROM users CROSS JOIN crashes USING(uid);
2:51 PM
The 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.
3:48 PM
(since these are fixed length strings)
a

asparamancer

10/09/2020, 11:50 AM
just saw this, will take a look - thank you both!