How to convert ticks to datetime in Elastic SQL?

Question

I want to group elastic entities by month. In order to do that I need to transform the Created column data type from ticks to datetime and then extract the month with MONTH_NAME function and then group. For this, I have managed to create the following query:

SELECT
  MONTH_NAME(CAST((Created - 621355968000000000)::double / 10000000 / 24 / 60 / 60 AS DATETIME)),
  COUNT(*)
FROM "{elastic-source}"
WHERE Created >= 638081280000000000 AND Created <= 638395776000000000
GROUP BY MONTH_NAME(CAST((Created - 621355968000000000)::double / 10000000 / 24 / 60 / 60 AS DATETIME))

This should give me the count of entities for 2023 grouped by the name of the month. The problem is that the CAST gives me 1970-01-01 for all entities. I followed the answer here Convert .NET Ticks to SQL Server DateTime, but changed the ticks I substract, because in SQL Server the date starts from 1900-01-01 (599266080000000000) and in elastic it starts from 1970-01-01 (621355968000000000).

I've also found that int cannot be casted to datetime in Elastic SQL. I've tried the following:

SELECT CAST(0 AS DATETIME)
SELECT CAST(1 AS DATETIME)
SELECT CAST(1000 AS DATETIME)

All results return 1970-01-01.

Marin Takanov · Accepted Answer · 2023-06-08T09:19:21.700

The problem was that I was dividing by 10000000 / 24 / 60 / 60 and the correct formula is to divide only by 10,000:

SELECT
  MONTH_NAME(CAST((Created - 621355968000000000) / 10000 AS DATETIME)) as month,
  COUNT(*)
FROM "{elastic-source}"
WHERE Created >= 638081280000000000 AND Created <= 638395776000000000
GROUP BY month

And about that:

I've also found that int cannot be casted to datetime in Elastic SQL. I've tried the following

This turns out to be incorrect. The problem here was that 0, 1 and 1000 were too small values and that's why they all ended as 1970-01-01.

I used this website to cast ticks to dateime: https://tickstodatetime.azurewebsites.net/

How to convert ticks to datetime in Elastic SQL?

1 Answers1