SQLite epoch date_time values to yyyy-mm-dd hh:mm:ss

[Hein]

Many of you probably noticed the SQLite database that come along with the TASK directory structure.
Those are for Replicate internal use, but may at time provide handy data like table number to table name mapping in dynamic_metadata.sqlite
A browser for those databases can readily be download from: DB Browser for SQLite

The time_stamps in those databases, like table load start_time and end_time in task_tables and time_stamp in task_audit, are in microseconds since the Unix Epoch (1-Jan-1970 0:0:0).

To report on those times you want to use for example:

select seq_num, time_stamp, datetime(time_stamp/1000000, 'unixepoch', 'localtime') "Local Time", event_type, status_text from task_audit  order by seq_num desc limit 10.

If one is stuck with the actual number you can use alternate tools like perl:

#perl -e "use Time::Local; print scalar localtime(1430377519933715/1000000)
Thu Apr 30 00:05:19 2015

Or SQLserver :

 select dateadd (microsecond, 1430377519933715, '1970-01-01')
 select dateadd (second, 1430377519933715/1000000, '1970-01-01')
 select dateadd (second, 1430377519, '1970-01-01')
--> 2015-04-30 07:05:19.000

Oddly enough, the microsecond method worked, but only once for me. Buglette? (SSMS 2012)
After that it started returning: Arithmetic overflow error converting expression to data type int.
Hmmmmm... Also note that the SQL server example returns UTC time.

Hope this helps someone, someday!
Hein


"1845" "1430442002014796" "2015-04-30 18:00:02" "1003" "Alternate backup file '\\dc01lobdbbkp\V$\MP_BKP8\NATIVE_LOGBACKUP\ERR\VD I_CFBC8D39-BB89-4FF8-A43B-4706749ABB3B_0' does not exist. Waiting 10 seconds..."
:

"1840" "1430377520354934" "2015-04-30 00:05:20" "1002" "Stream component 'st_0_S_DSS10_ERR_IL_PRD' terminated"
"1839" "1430377520323733" "2015-04-30 00:05:20" "1002" "Stream component failed at subtask 0, component st_0_S_DSS10_ERR_IL_PRD "
"1838" "1430377520261330" "2015-04-30 00:05:20" "1002" "Error executing source loop"
"1837" "1430377520198927" "2015-04-30 00:05:20" "1002" "Endpoint is disconnected"

[Hein]

With Replicate 5.1 we can now manipulate the TASK, FULL-LOAD and CDC tables.
Now we can HIDE the often redundant 'type'='Unidirectional'
And you can re-order, and most importantly one can now also export to CSV file.
Look around for yourself : - Right click on header -

This is great!
One gotcha on Table statistics is that the time is that omni-present replicate way of storing the time as a 64 bit int representing the number of micro-seconds-since-beginning-of-utc-time
That's perfect for me, but I can imagine other struggle some with that.
Not to worry...

One easy formula converts this to the EXCEL notion of time which is (drumroll...) the number of days since 1/0/1900
Hours and minutes are fractional days. So xxxx.25 is 6 in the morning, UTC

For EST, the formula to use is :

• =((<cell_with_microseconds_since_1970>/1000000)/86400)+25568+5/6
• now use FORMAT --> FORMAT CELLS --> CUSTOM --> m/d/yyyy h:mm

Works for me!


btw... If you are concerned about the 0-th day of January... well there is worse.
It also counts 1/29/1900, a day which never existed according to the civilized world way of doing leap years.
Leap: if divisible by 4, unless divisible by 100, unless divisible by 400. (yes, yes, no)

Hein.







=((<cell_with_microseconds_since_1970>/1000000)/86400)+25568.8333