Support Infinity values for Date type

Phoenix Forum Questions / Help
,
1

Trying to wrap existing Date to support infinity special value

Ecto: 2.2.9

Implemented this

defmodule Utils.Ecto.Types.Date do
  @behaviour Ecto.Type

  @doc false
  def type, do: :date

  @doc false
  def cast("infinity"), do: {:ok, :"infinity"}
  def cast("-infinity"), do: {:ok, :"-infinity"}
  def cast(value), do: Ecto.Type.cast(:date, value)

  @doc false
  def load("infinity"), do: {:ok, :infinity}
  def load("-infinity"), do: {:ok, :"-infinity"}
  def load(value), do: Ecto.Type.load(:date, value, nil)

  @doc false
  def dump(:infinity), do: {:ok, "infinity"}
  def dump(:"-infinity"), do: {:ok, "-infinity"}
  def dump(value), do: Ecto.Type.dump(:date, value, nil)
end

When trying to insert a record getting

  1) test support infinite values (FooBar.Hello)
     test/foo_bar/hello_test.exs:63
     ** (CaseClauseError) no case clause matching: {"-infinity"}
     stacktrace:
       (foo_bar) lib/ecto/types/date.ex:715: FooBar.PostgresTypes.encode_params/3
       (postgrex) lib/postgrex/query.ex:45: DBConnection.Query.Postgrex.Query.encode/3
       (db_connection) lib/db_connection.ex:1079: DBConnection.describe_run/5
       (db_connection) lib/db_connection.ex:1150: anonymous fn/4 in DBConnection.run_meter/5
       (db_connection) lib/db_connection.ex:592: DBConnection.prepare_execute/4
       (ecto) lib/ecto/adapters/postgres/connection.ex:86: Ecto.Adapters.Postgres.Connection.execute/4
       (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
       (ecto) lib/ecto/adapters/sql.ex:542: Ecto.Adapters.SQL.struct/8
       (ecto) lib/ecto/repo/schema.ex:547: Ecto.Repo.Schema.apply/4
       (ecto) lib/ecto/repo/schema.ex:213: anonymous fn/14 in Ecto.Repo.Schema.do_insert/4
       (ecto) lib/ecto/association.ex:556: Ecto.Association.Has.on_repo_change/4
       (ecto) lib/ecto/association.ex:338: anonymous fn/7 in Ecto.Association.on_repo_change/6
       (elixir) lib/enum.ex:1899: Enum."-reduce/3-lists^foldl/2-0-"/3
       (ecto) lib/ecto/association.ex:335: Ecto.Association.on_repo_change/6
       (elixir) lib/enum.ex:1899: Enum."-reduce/3-lists^foldl/2-0-"/3
       (ecto) lib/ecto/association.ex:301: Ecto.Association.on_repo_change/3
       (ecto) lib/ecto/repo/schema.ex:708: Ecto.Repo.Schema.process_children/4
       (ecto) lib/ecto/repo/schema.ex:774: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
       (db_connection) lib/db_connection.ex:1374: DBConnection.transaction_nested/2
       (db_connection) lib/db_connection.ex:1234: DBConnection.transaction_meter/3

Tried to read the source but it is too much metacode inside which I can’t understand.

2

Looks like postgrex isn’t able to encode an "infinity" date string, though I might be mistaken. I don’t see any mention of it in https://github.com/elixir-ecto/postgrex/blob/master/lib/postgrex/extensions/date.ex

But there seems to be some confusion with your usage of atoms and strings.

"infinity" is not equal to :"infinity", and your code seems to use atoms or strings interchangeably.

EDIT: Nope, postgrex doesn’t support infinity dates at the moment: https://github.com/elixir-ecto/postgrex/issues/274

2 Likes
3

This is very bad :disappointed_relieved:

4

I tried to add missing match for extension

defmodule Utils.Ecto.Adapters.Postgres.Date do
  @moduledoc false
  import Postgrex.BinaryUtils, warn: false
  use Postgrex.BinaryExtension, send: "date_send"

  @gd_epoch :calendar.date_to_gregorian_days({2000, 1, 1})
  @max_year 5874897

  def init(opts), do: opts

  def encode(_) do
    quote location: :keep do
      {year, month, day} when year <= unquote(@max_year) ->
        date = {year, month, day}
        <<4 :: int32, :calendar.date_to_gregorian_days(date) - unquote(@gd_epoch) :: int32>>
      "-infinity" ->
        IO.puts "AWWWH CHUBAKA SAYS: #{x}"
        <<4 :: int32, :calendar.date_to_gregorian_days({2019, 3, 8}) - unquote(@gd_epoch) :: int32>>
      "infinity" ->
        IO.puts "AWWWH CHUBAKA SAYS: #{x}"
        <<4 :: int32, :calendar.date_to_gregorian_days({2019, 3, 8}) - unquote(@gd_epoch) :: int32>>
    end
  end

  def decode(_) do
    quote location: :keep do
      <<4 :: int32, days :: int32>> ->
        :calendar.gregorian_days_to_date(days + unquote(@gd_epoch))
      "-infinity" ->
        IO.puts "YOU SHALL NOT PASS: #{x}"
        :calendar.gregorian_days_to_date(0 + unquote(@gd_epoch))
      "infinity" ->
        IO.puts "YOU SHALL NOT PASS: #{x}"
        :calendar.gregorian_days_to_date(0 + unquote(@gd_epoch))
    end
  end
end

On insert I put dummy values to check what is the output from db.

CREATE TABLE sandbox(
    id serial primary key,
    date date
);

INSERT INTO sandbox (date) VALUES ('infinity');

Then

defmodule Sand do
  use Ecto.Schema

  schema "sandbox" do
    field :date, Scene.Utils.Ecto.Types.Date
  end
end

And then

s = Repo.one(Sand)
%Sand{
  __meta__: #Ecto.Schema.Metadata<:loaded, "sandbox">,
  date: %Inspect.Error{
    message: "got ArgumentError with message \"argument error\" while inspecting %{__struct__: Date, calendar: Calendar.ISO, day: 11, month: 7, year: 5881610}"
  },
  id: 1
}

But

iex(8)> %{calendar: Calendar.ISO, year: year, month: month, day: day} = s.date
%Inspect.Error{
  message: "got ArgumentError with message \"argument error\" while inspecting %{__struct__: Date, calendar: Calendar.ISO, day: 11, month: 7, year: 5881610}"
}
iex(9)> year
5881610

And the doc says that the max date is 5874897 AD :thinking:

5

I do wonder: What is your intended use for this?

6

Hmm. Unbounded dates? :slight_smile:

For example:

SELECT * FROM xxx WHERE dateRange @> someDate;
7

If I’m not mistaken, this won’t work as the database DATE type cannot be stored as the string "infinity". The column would have to be a varchar and then you would have to perform date parsing in the where clause (and indexing by it would be poor as well). The best thing I can recommend is when the date is exactly equal to the highest and lowest values in the datetime, those are markers for infinity and negative infinity (looks like 4713 BC and 294276 AD for postgres). Try making your custom type use those values instead.
https://www.postgresql.org/docs/9.2/datatype-datetime.html

1 Like
8

What about using NULL for the purpose of infinity? Wouldn’t that be a solution? Then using something like SELECT * FROM xxx WHERE dateRange @> someDate OR someDate IS NULL;.

9

I can’t speak for your particular domain of course, but in my world I generally tend to use 9999-12-31 as the “infinity” date… I imagine if I had to decide on “-infinity”, I’d go for 0001-01-01.

Though that’s with the reservation that I generally just have to deal with event timestamps over the lifetime of the company where I work.

Thus… you could potentially have your extension translate :infinity to 9999-12-31, etc.

2 Likes
10

NULL is not less or greater than now() it just gives you nothing. So it won’t work.

11

@vlad.grb This is why he uses OR someDate IS NULL

2 Likes
12

I think he also wants negative infinity, which won’t have a way to represent it using null. Using the database’s min and max date is the only way to do this.

13

I want to keep the data consistent, use exclude, check for containing, use both negative and positive infinities and have an index on this. So sure I could make multiple OR assuming that null has priority depending on the field like start_at, end_at and if end_at is null then it means an infinity. But I think that using infinity special values is much easier and more elegant way to concentrate on the general logic than on Boolean algebra.

14

If you are using Postgres it might be possible to define your own compound data type. You will probably lose some of the built-in datetime-related functionality though, probably.

I currently like @jwarlander’s solution (of using two dates that are not relevant to your business domain because they lie so far in the past/future as -infinity and infinity respectively) because that will keep all existing date-logic working.

3 Likes
15

Could you make use of the Postgres type tsrange? This would be instead of storing two dates separately.

https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN

I believe it even supports unbounded ranges:
https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-INFINITE

1 Like
16

Could you make use of the Postgres type tsrange ?

That was the plan. daterange uses date as a scalar type for the range and infinity is still the issue.

1 Like
17

As you’ve shown it’s possible to build a custom Postgrex extension which could even map Postgres infinity to Elixir :infinity atom and have a Ecto custom type on top of that. However, this :infinity value wouldn’t work with any of the Date.* functions from standard library, e.g. you couldn’t do Date.compare(date, :infinity) which I think will severely limit usefulness of this.

I think limiting range of possible values to 0000-01-01/9999-12-31 (or whatever you want to arbitrarily pick) is a pragmatic choice.

I’d be curious to hear what’s your actual use case for an infinity value, do you have a particular table and column name in mind that would hold such value?

Btw, regarding:

%Inspect.Error{
message: “got ArgumentError with message "argument error" while inspecting %{struct: Date, calendar: Calendar.ISO, day: 11, month: 7, year: 5881610}”
}

This works on recent Elixir versions:

% elixir --version
Elixir 1.9.0-dev
iex> %{__struct__: Date, calendar: Calendar.ISO, day: 11, month: 7, year: 5881610}
~D[5881610-07-11]
1 Like
18

This works on recent Elixir versions:

For sure I can’t jump from 1.6 to 1.9 :slight_smile:

I’d be curious to hear what’s your actual use case for an infinity value

In general it is a schedule with weekly timings. And the schedule could be limited ex: from 1 March to 1 June and limitless aka yearly.

I think limiting range of possible values to 0000-01-01/9999-12-31 (or whatever you want to arbitrarily pick) is a pragmatic choice.

Yeah, I am thinking it is only the existing way right now. But I hope that core team took a note on this since it is useful functionality and as I imagine is just a matter of:

field_db_value = case date do
  Date.positive_infinity -> "infinity"
  Date.negative_infinity -> "-infinity"
  date -> "date parts as was"
end
19

Not exactly.
Postgrex tries to convert to NaiveDateTime with Calendar.ISO being its default calendar.
Since the notion of “infinity” in postgres is just another value (link), it arrives as some date in the super distant future (eg. year 294277). This fails because of this validation in Calendar.ISO, which just does not allow years greater than 9999.

2 Likes
20

I stumbled upon this issue too. -infinity and +infinity are perfectly valid dates in Postgres and it brings a lot of advantages using them (default values especially) rather than using fixed dates.

I could understand a problem if they came back as actual strings on the adapter, but instead they come back as valid timestamps, both negative and positive, Calendar.ISO however is limit the year to -9999 -> 9999, but this is very arbitrary.
What if I’m modeling a game set into the future, or in a time traveling fashion? Or more simply, what if I’m modeling a website about history? Wikipedia says that human existed over 2.8 millions years ago.

1 Like