Skip to main content

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

  • June 24, 2014
  • 5 replies
  • 18 views
K_Krutiy
Forum|alt.badge.img
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 ?



    5 replies

    Sajan_Sangraula
    Forum|alt.badge.img+1
    • Sajan_Sangraula
    • Participating Frequently
    • June 24, 2014
    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
    Forum|alt.badge.img
    • K_Krutiy
    • Author
    • Participating Frequently
    • June 24, 2014
    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
    Forum|alt.badge.img+1
    • Sajan_Sangraula
    • Participating Frequently
    • June 24, 2014
    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
    Forum|alt.badge.img
    • Sharon_Cutter
    • Participating Frequently
    • June 25, 2014
    Try this:

    select to_timestamp ( 456548442250721 / 1000000.0 + 946684800);


    Daniel_Leybovic
    Forum|alt.badge.img+1
    • Daniel_Leybovic
    • Participating Frequently
    • June 25, 2014
    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)


    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement