By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,710 Members | 1,626 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,710 IT Pros & Developers. It's quick & easy.

timestamp and date behaviour with '-infinity'

P: n/a
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 a ASC;
a | ?column?
---+----------
| -inf
| inf
(2 rows)

church=# select '-infinity'::timestamp::date as a, '-inf' UNION select 'infinity'::timestamp::date as a, 'inf' ORDER BY a DESC;
a | ?column?
---+----------
| -inf
| inf
(2 rows)

church=# select '-infinity'::timestamp::date::timestamp;
timestamp
-----------

(1 row)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Mon, 30 Aug 2004, Russell Smith wrote:
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?


It looks to me that it turns into a NULL date. I'm not sure why it's
doing that rather than an error, though.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2

P: n/a
Russell Smith <mr*****@pws.com.au> writes:
Timestamps support infinity. However if appears dates do not.


There is a TODO item to fix that, but it hasn't gotten to the top of
anyone's priority list ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.