Calculate age from cal::local_date

[divan]

So I have a birth date as cal::local_date in the table, and want to create a computed property age that returns the age in years at the current date.

The workign solution I came up with is:

SELECT cal::to_local_datetime(datetime_of_statement(), 'UTC') - cal::to_local_datetime(to_str(Person.birth_date)++'T12:00:00');

but it's incorrect as it's using relative_duration. I could not find how to "cast" local_date to datetime, so ended up trying string representation of local_date concatenated with noon time:

to_str(Person.birth_date)++'T12:00:00+00'

but when I run:

SELECT datetime_of_statement() - to_datetime(to_str(Person.birth_date)++'T12:00:00+00')

it yields an error I can't understand:

NumericOutOfRangeError: interval field value out of range: "2396512768.816903"

I tried to play with formatting options (like to_datetime(to_str(Person.birth_date)++'T12:00:00+00', 'YYYY-MM-DDTHH:MI:SS TZH')), but received the same error.

So, two questions:

1. Is it even a good idea to calculate age in computeds or better offload it to client code?
2. How to calculate it properly and correctly?

[Syzuna]

The docs on to_datetime have a section about how to convert local_datetime to datetime.

https://www.edgedb.com/docs/stdlib/datetime#function::std::to_datetime

You just need to scroll a bit

[divan]

Yes, but I need to convert local_date (not local_datetime).

[elprans]

You can cast local_date to local_datetime: <cal::local_datetime>User.birth_date

[elprans]

but it's incorrect as it's using relative_duration

Hm, how is it incorrect?

Here's how I'd do it:

SELECT 
  duration_get(
    cal::duration_normalize_days(
      cal::to_local_datetime(datetime_of_statement(), 'UTC') 
      - <cal::local_datetime>Person.birth_date
    ), 
    'year',
  );