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

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 = 

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 ?


  • 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)

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

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

  • Try this:

    select to_timestamp ( 456548442250721 / 1000000.0 + 946684800);

  • 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);
    1970-01-01 02:00:00
    (1 row)

    daniel=> select to_timestamp ( 456548442250721 / 1000000.0 + 946684800);
    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);
    2014-06-20 06:00:42.250721
    (1 row)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file