Hi Martin,

If I am going to edit the scripts to run, I will for sure use the "day" function.

I am trying to avoid as much as possible to manually edit the user scripts and try to run it as is,

hence the question of possible introduction of a custom cast function that maps the interval to int so the syntax (cast interval as int) will find an appropriate function to call.

Since I am becoming more and more familiar with the MAL language, the calculator mal-module is responsible for providing the function for the casting between different types.

ex:

COMMAND batcalc.timestamp(X_0:bat[:str], X_1:bat[:oid], X_2:int, X_3:int):bat[:timestamp];

COMMENT "cast to timestamp and check for overflow";

If I introduce a new mal-function that takes a :bat[:timestamp] and returns :bat[:int], how to instruct the engine or the sql layer that a new map exists and it can be called on cast(interval as int)?

Thank you.

On Wed, Aug 3, 2022 at 1:34 PM Martin van Dinther <dinther@monetdbsolutions.com> wrote:

Hi,

As of Oct2020 release you can not use cast(interval as int) anymore.
For more information on this change read:
https://www.monetdb.org/documentation-Jan2022/admin-guide/release-notes/oct2020/
Search on "Removed compatibility between interval types and other
numeric types"

As you mention you should now use function:
sys."day"(day_interval)
which returns a bigint.
So for your example:
sys."day"(date1-date2)
or
cast(sys."day"(date1-date2) as int)
if you want it to return an int.

However you can cast any data to a string.
So cast day_interval data to a string, which may be cast to a decimal as
it represents a decimal, which may be cast to an int:
cast(cast(cast(date1-date2 as string) as decimal) / 86400 as int)

I assume you want the difference in number of days between 2 dates and
not the number of seconds. Hence the division by 86400 (= 60 * 60 * 24).

Hope this helps in your migration.

Regards,
Martin

On 01-08-2022 14:52, imad hajj chahine wrote:
> Hi,
>
> I am currently migrating monetdb from an old version to the current
> release, and I noticed that the difference of 2 dates return a
> day_interval and cannot be cast to an int and I have to use the "day"
> function for this purpose.
>
> This is fine for newly written scripts, but for old scripts I am
> hoping to avoid a rewrite.
>
> ex: cast(date1-date2 as int) return an error: types day_interval(4,0)
> and int(32,0) are not equal
>
> How to define a cast function from day_interval to int to avoid any
> changes to the old scripts?
>
> _______________________________________________
> users-list mailing list -- users-list@monetdb.org
> To unsubscribe send an email to users-list-leave@monetdb.org
>