Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Convert .NET Ticks to Vertica DateTime

Hi,
I'm trying to convert a .NET Tick int value to a DateTime format.

For example, 637927152990660008 is 2022-07-06 15:41:39.066
Found the following code that works in SQL Server:-

DECLARE @Ticks bigint
set @Ticks = 637927152990660008 -- 2022-07-06 15:41:39.066
select DATEADD(ms, ((@Ticks - 599266080000000000) -
FLOOR((@Ticks - 599266080000000000) / 864000000000) * 864000000000) / 10000,
DATEADD(d, (@Ticks - 599266080000000000) / 864000000000, '01/01/1900')) +
GETDATE() - GETUTCDATE()

2022-07-06 15:41:39.067

Any idea how to convert this to run in Vertica, was hoping to change DATEADD to TIMESTAMPADD, but it does not like that?
DateTimeTicks is an integer column on the database holding the data that needs converting.

select TIMESTAMPADD (ms, ((DateTimeTicks - 599266080000000000) -
FLOOR((DateTimeTicks - 599266080000000000) / 864000000000) * 864000000000) / 10000,
TIMESTAMPADD (d, (DateTimeTicks - 599266080000000000) / 864000000000, '01/01/1900')) +
GETDATE() - GETUTCDATE()
from TickHistory.futures
where DateTimeTicks = 637927152990660008;
ERROR 3457: Function catalog.timestampadd(unknown, numeric, unknown) does not exist, or permission is denied for catalog.timestampadd(unknown, numeric, unknown)
HINT: No function matches the given name and argument types. Candidates are:
catalog.timestampadd(datepart::varchar, count::int, start‑date::timestamptz)
catalog.timestampadd(datepart::varchar, count::int, start‑date::timestamp)
dbadmin=>

Tim

Answers

  • moshegmosheg Administrator

    TO_TIMESTAMP Converts a string value or a UNIX/POSIX epoch value to a TIMESTAMP type.
    See: https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TO_TIMESTAMP.htm

    For example: 
    \set lepoch `date "+%s"`
    
    \echo :lepoch
    1663081745
    
    SELECT TO_TIMESTAMP(:lepoch);
        TO_TIMESTAMP
    ---------------------
     2022-09-13 11:09:05
    (1 row)
    
  • Hi Tim,

    Seems it's easier than that:

    select timestamp '0001-01-01 00:00:00.000000' + (637927152990660008 / 10 * interval '1 microsecond');
    
    2022-07-06 14:41:39.066
    

    There may be some time zone related stuff to check, but this seems quite good.

  • Thanks for the solutions, the function "TO_TIMESTAMP" does not work in this case as the data is a .NET Tick integer.
    Not sure if there is a way in SQL to convert .NET tick into a UNIX epoch and then TO_TIMESTAMP might be a good option as it should then handle the summer/winter time conversion?

    The second solution works well, will just need to take into account day light saving time.

    Regards
    Tim

  • moshegmosheg Administrator
    SET TIME ZONE TO 'Europe/Rome';
    SELECT CURRENT_TIME(0) Current_time_at_Rome_Italy;
     Current_time_at_Rome_Italy
    ----------------------------
     22:19:08+02
    (1 row)
    
    select TO_TIMESTAMP_TZ(floor((637927152990660008 - 621355968000000000) / 10000000));
        TO_TIMESTAMP_TZ
    ------------------------
     2022-07-06 16:41:39+02
    (1 row)
    
    SET TIME ZONE TO DEFAULT;
    SHOW TIMEZONE;
       name   |     setting
    ----------+------------------
     timezone | America/New_York
    (1 row)
    
    SELECT CURRENT_TIME(0) Current_time_at_current_timezone;
     Current_time_at_current_timezone
    ----------------------------------
     16:19:08-04
    (1 row)
    
    select TO_TIMESTAMP_TZ(floor((637927152990660008 - 621355968000000000) / 10000000));
        TO_TIMESTAMP_TZ
    ------------------------
     2022-07-06 10:41:39-04
    (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.