Skip to main content
Question

Convert .NET Ticks to Vertica DateTime

  • September 13, 2022
  • 4 replies
  • 11 views
Tim_1
Forum|alt.badge.img+1
  • Tim_1
  • Participating Frequently

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

    4 replies

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • September 13, 2022

    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)
    VValdar
    Forum|alt.badge.img+1
    • VValdar
    • Participating Frequently
    • September 13, 2022

    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.

    Tim_1
    Forum|alt.badge.img+1
    • Tim_1
    • Author
    • Participating Frequently
    • September 14, 2022

    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

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • September 14, 2022
    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)
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement