DATE_TRUNC with IRIS SQL
Most of the databases with SQL access offers the DATE_TRUNC function or analogs. This helps to easily truncate a date by any part while keeping the DateTime in a valid format
.png)
I found it quite tricky to implement the same function with IRIS, some intervals can be done quite easily, when such intervals as quarter, week, decade and so on I did not find how to implement, yet
SELECT
CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-01-01 00:00:00' ) AS DATETIME ) "year",
CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-01 00:00:00' ) AS DATETIME ) "month",
CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-DD 00:00:00' ) AS DATETIME ) "day",
CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-DD HH:00:00' ) AS DATETIME ) "hour",
CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-DD HH:DD:00' ) AS DATETIME ) "minute",
CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-DD HH:DD:SS' ) AS DATETIME ) "second",
CAST ( some_date AS DATETIME ) "original"
FROM ( SELECT '2023-03-28 14:48:12.183590' AS some_date ).png)
Any ideas on how to implement the missing intervals?
And I expecting SQL only ways, I know I can write some SQL Procedure, but it's not the case
Comments
Hi Dmitry, we've recently been working on this function and a bunch of SQL optimizer enhancements to leverage it in query processing. I can't promise an exact release date, but it definitely will be this calendar year.
Sounds wonderful
SELECT {fn WEEK('2004-02-25')} AS Week, {fn DAY('2004-02-25')} as Day,{fn DAYOFWEEK('2004-02-25')} as DayOfWeek,{fn Year('2004-02-25')} as Year,{fn Quarter('2004-02-25')} as Quarter,{fn DAYOFYEAR('2004-02-25')} as DayOfYearDoc : https://docs.intersystems.com/iris20223/csp/documatic/%25CSP.Documatic…
not all your requirements but it's a start
It's a bit more complicated for this task.
It always should return the full datetime, for instance if I trunc to quarter, it should return 2023-04-01 for the current day