471,854 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,854 software developers and data experts.

timestamp and date behaviour with '-infinity'

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
2 4159
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
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.

Similar topics

2 posts views Thread by praba kar | last post: by
3 posts views Thread by Shelly | last post: by
2 posts views Thread by Jim Piellier | last post: by
7 posts views Thread by Ross | last post: by
2 posts views Thread by Campano, Troy | last post: by
NeoPa
reply views Thread by NeoPa | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.