execution_engine_profiles value for counters "start time" and "end time"

K_Krutiy

table execution_engine_profiles has many counters.

in documentation value for counter "start time" described as "Time (timestamp) when HP Vertica started to process the operation."

when I query table execution_engine_profiles for counter "start time" I see number = 
456548442250721


I tried 

select TO_timestamp(456548442250721);

select date(456548442250721);

but got some unrealistic results. so obviously it was not it.


how do I convert this number to actual and correct timestamp ?

Sajan_Sangraula

All the time values in the execution profiles system table (send time, receive time etc.) are in microseconds - the start time should also be in microseconds since epoch. Please try to_timestamp(start_time/1000000)

/Sajan

K_Krutiy

Now I get realistic, but still not correct timestamp.

When I run 

select TO_timestamp(456548442250721/1000000);

I get  "1984-06-20 03:00:42.250721"

but I know that my timestamp should be between "6/20/2014  2:48:25 AM" and "6/20/2014  3:01:59 AM"

looks like I am off by 30 years. Funny thing that month, day and time look reasonable.

Any thoughts ?

Sajan_Sangraula

Looks like the milliseconds start from the turn of the millennium, and not from the Linux epoch start time of 1970. That would explain the 30 yrs offset .
 I will try to run some sample data and check, or someone from Vertica can confirm if the microseconds indeed start from 2000?

/Sajan

Sharon_Cutter

Try this:

select to_timestamp ( 456548442250721 / 1000000.0 + 946684800);

Daniel_Leybovic

Hi!

Sharon solution is correct,

* TS div 1000000 - because its internal representation in microseconds, while function should get seconds.
* ADD 946684800  - because epoch is from '1970-01-01 02:00:00', i.e. interval of 0 seconds is  '1970-01-01 02:00:00'.

daniel=> select to_timestamp(0);
    to_timestamp     
---------------------
 1970-01-01 02:00:00
(1 row)

daniel=> select to_timestamp ( 456548442250721 / 1000000.0 + 946684800);
        to_timestamp        
----------------------------
 2014-06-20 06:00:42.250721
(1 row)

--
-- without a "magic number" - 946684800
--
daniel=> select to_timestamp((456548442250721 - timestamptz_to_internal(to_timestamp(0)))/1000000);
        to_timestamp        
----------------------------
 2014-06-20 06:00:42.250721
(1 row)