Timestamps support infinity. However if appears dates do not.
When timestamps are cast to dates, there is no output. Is this an acceptable option or not?
Below are a number of examples showing what I am experiencing.
The last own shows how converting timestamps to dates and then ordering doesn't give you the
order you want. Maybe you should just order by the timestamp to begin with. However
Date does not understand infinity at all.
So as much as what I have said is confusing.
1. Is the output acceptable?
2. should there be infinity support for dates?
3. if not, how should -infinity timestamp converted to a date.
4. Am I missing the point here.
Regards
Russell Smith
church=# select '-infinity'::timestamp;
timestamp
-----------
-infinity
(1 row)
church=# select '-infinity'::timestamp::date;
date
------
(1 row)
church=# select '*' || '-infinity'::timestamp::date || '*';
?column?
----------
(1 row)
church=# select '*' || '-infinity'::timestamp::date::text || '*';
?column?
----------
(1 row)
church=# select '*' || '-infinity'::timestamp || '*';
?column?
-------------
*-infinity*
(1 row)
church=# select '*' || '-infinity'::timestamp::date || '*';
?column?
----------
(1 row)
church=# select '-infinity'::timestamp::date as a, '-inf' UNION select 'infinity'::timestamp::date as a, 'inf' ORDER BY
aASC;
a | ?column?
---+----------
| -inf
| inf
(2 rows)
church=# select '-infinity'::timestamp::date as a, '-inf' UNION select 'infinity'::timestamp::date as a, 'inf' ORDER BY
aDESC;
a | ?column?
---+----------
| -inf
| inf
(2 rows)
church=# select '-infinity'::timestamp::date::timestamp;
timestamp
-----------
(1 row)