Support Infinity values for Date type

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 postgrex/lib/postgrex/extensions/date.ex at master · elixir-ecto/postgrex · GitHub

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: postgrex can't handle all possible postgres date/timestamp/timestamptz values · Issue #274 · elixir-ecto/postgrex · GitHub

This is very bad

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

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

Hmm. Unbounded dates?

For example:

SELECT * FROM xxx WHERE dateRange @> someDate;

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.

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;.

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.

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

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