The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Converting Epoch time in milliseconds to Time stamp with Milliseconds

Hi,

 

We have an incoming data file containing field values as below. We believe this are EPOCH timestamp values in milliseconds. Can someoen please help convert these to standard time stamp values. Standard 'EXTRACT' function doesn't seem to work here.

 

1447103098937

1447103098765

1447103098915

1447103098980

 

Thanks

Comments

  • Hi!

     

    Mathematically its not so simple.

    An example gmtime source code: http://www.cise.ufl.edu/~cop4600/cgi-bin/lxr/http/source.cgi/lib/ansi/gmtime.c

     

     

    TO_TIMESTAMP

    Converts a string value or a UNIX/POSIX epoch value to a TIMESTAMP type.

     

     

    dbadmin=> select to_timestamp(1447103098937 / 1000);
          to_timestamp       
    -------------------------
     2015-11-09 16:04:58.937
    (1 row)

    Time: First fetch (1 row): 8.205 ms. All rows formatted: 8.272 ms
    dbadmin=> select to_timestamp(1447103098980 / 1000);
          to_timestamp      
    ------------------------
     2015-11-09 16:04:58.98
    (1 row)

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.