asparamancer
10/07/2020, 3:42 AMseph
10/07/2020, 2:21 PM2020-09-11 21:53:01.379 -0400
2020-09-11 21:53:01.379 -04:00
SELECT strftime('%s','2020-09-11 21:53:01.379 -04:00');
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 |
+-------------------------------+--------------------------------------------------------------------------------------------------------+
fritz
10/07/2020, 2:50 PMSELECT strftime('%s', (SUBSTR(datetime, 0, 24) || SUBSTR(datetime, 25, 3) || ':' || SUBSTR(datetime,28,2)) ) AS epoch FROM users CROSS JOIN crashes USING(uid);
seph
10/07/2020, 3:48 PMasparamancer
10/09/2020, 11:50 AM