Convert UNIX timestamp (epoch time) to date/time in an Analysis

Content Authoring
1

(imported topic written by tostroff91)

Is there a Windows Inspector that can display epoch time via an Analysis? I need to parse a local logfile created by backup software that records each backup (and its associated status code) in this fashion. I will then convert this human readable date/time and status code into a more meaningful backup report for each client.

2

(imported comment written by BenKus)

You mean the epoch time in terms of seconds since 1970?

Ben

3

(imported comment written by tostroff91)

Yes - I’m hoping a Windows Inspector does this on the fly without me having to run it as a script. Here’s a Web link that performs the conversion I’d like to have in my Analysis: http://www.epochconverter.com/

4

(imported comment written by BenKus)

Hey Tom,

Actually, this turns out to be pretty simply in relevance:

q: 1236707740 * second + “01 Jan 1970 00:00:00” as local time
A: Tue, 10 Mar 2009 17:55:40 -0700

Ben

5

(imported comment written by tostroff91)

Thanks, that formula works fine in Q&A, now I could use some help doing a substitution within the Analysis. I’m parsing the data from Line 5 of the relevant file, and getting this (variable) value: 1236718130. How can I display the converted date/time value of this string between and ?

6

(imported comment written by BenKus)

Maybe try something like this:

q: (it * second + “01 Jan 1970 00:00:00” as local time) of (preceding text of first “” of following text of first “” of (line 7 of file “C:\blah”) as integer)

Ben

7

(imported comment written by tostroff91)

That did the trick, thanks Ben!

Tom

8

[Holy thread resurrection!]

I don’t know if there’s a better way to do this in 2021, but this method still works great!

1 Like
9

I still use this method reading timestamps out of SQLite databases…

1 Like
10

I’m sharing here a relevance suggested by a customer who uses to parse the ActionHitory.db and returns the IssuedTime in a readable format

((column “IssuedTime” of it as string as integer) * second + “01 Jan 1970 00:00:00 -0000” as local time,column “ActionName” of it, column “IssuerName” of it, column “ActionID” of it) of rows of statements (“SELECT IssuedTime,ActionName, IssuerName, ActionID FROM ACTION_HISTORY”) of sqlite databases of files “ActionHistory.db” of folders “__Global” of data folders of client

2 Likes
11

In case the issue of then using the date in something like Excel, you can format it to your preferred regional format, and can even leverage the great tip @brolly33 shared recently in A new relevance tidbit: Date with dash.

Q: (((concatenation "-" of (day_of_month of it as two digits as string;month of it as two digits as string;year of it as string) of date (local time zone) of it) & " " & (concatenation ":" of ((two digit hour of it);(two digit minute of it);(two digit second of it)) of time (local time zone) of it)) of ((column "IssuedTime" of it as string as integer) * second + "01 Jan 1970 00:00:00 -0000" as local time)) of rows of statements ("SELECT IssuedTime,ActionName, IssuerName, ActionID FROM ACTION_HISTORY") of sqlite databases of files "ActionHistory.db" of folders "__Global" of data folders of client
A: 29-11-2016 14:59:17
A: 20-03-2018 13:38:50
A: 29-03-2018 10:09:43
A: 26-08-2019 11:27:25
1 Like
12

A tip on using the ‘format’ operator to change the ordering of day/month/year and delimiters and such also at Tip - ‘format’ and time/date

2 Likes