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

(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.

(imported comment written by BenKus)

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

Ben

(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/

(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

(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 ?

(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

(imported comment written by tostroff91)

That did the trick, thanks Ben!

Tom

[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

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

1 Like

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

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

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