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/oct2... 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