Channels
android_tests
apple-silicon
arm-architecture
auditing-warroom
aws
carving
code-review
community-feeds
core
darkbytes
doorman
ebpf
eclecticiq-polylogyx-extension
extensions
file-carving
fim
fleet
fleet-dev
fleetosquery
foundation
fuzzing
general
golang
goquery
infrastructure
jobs
kolide
linen-dev
linux
macos
officehours
osctrl
plugins
process-auditing
querycon
queryhub
random
selfgroup
sql
tls
uptycs
vendor-feeds
website
windows
zeek
zentral
zercurity
Title
a
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 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 PMI see a hacky route.
The column is coming in as
2020-09-11 21:53:01.379 -0400sqlite expects
2020-09-11 21:53:01.379 -04:00So you’d need to string manipulate the timesonze into the expected format.
And then
SELECT strftime('%s','2020-09-11 21:53:01.379 -04:00');I might be reasonable to add a column with the datatime in unix time directly. Not totally sure what I think
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:
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
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 PMI think substr is a little cleaner than regex.
(since these are fixed length strings)
a
asparamancer
10/09/2020, 11:50 AMjust saw this, will take a look - thank you both!