Convert .NET Ticks to Vertica DateTime

Tim_1

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

mosheg

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

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

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

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)