Channels
  • a

    asparamancer

    1 year ago
    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

    1 year ago
    I see a hacky route.
    The column is coming in as
    2020-09-11 21:53:01.379 -0400

    sqlite expects
    2020-09-11 21:53:01.379 -04:00

    So 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

    1 year ago
    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);

    The important thing to remember @User is that you *must* join against users or you will be dropping data
  • s

    seph

    1 year ago
    I think substr is a little cleaner than regex.
    (since these are fixed length strings)