Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and all the rest are simply date. I can't change this because I don't have admin access, and in fact I'm only querying the view. Postgres is fairly new to me, so I don't really understand how the dates and times work with each other.

When I try and query the server with timestamp with time zone by casting the timestamp as a date:

...
WHERE
    (
        status_code = '30000'
        OR status_code = '30005'
    )
AND CAST(expiration AS DATE) > now()

It works, but using the same query on the servers where expiration is already a date fails:

[Err] ERROR: invalid input syntax for type date: "No End Date"

Any help would be appreciated, I'd really rather not hard code an exception for this one DB server.

share|improve this question
    
Are you sure expiration is a timestamp column? Sounds like it's a convertion problem caused by the CAST operator –  a_horse_with_no_name Apr 3 '12 at 21:46
add comment

1 Answer

up vote 4 down vote accepted

This should never fail (I simplified a bit):

WHERE status_code IN ('30000','30005')
AND   expiration > now() 

PostgreSQL can compare date and timestamp (with or without time zone) automatically. If one is a date it is cast to timestamp automatically (0:0 hours).

The error message tells a different story. You are actually trying to input a date with invalid syntax. Your question is not telling the whole story.

I wrote a detailed answer to explain the handling of timestamps with or without time zone in PostgreSQL recently - if that should be an issue here.


As it turns out, expiration is a text column. You need to cast it to date or timestamp (whichever fits your need). If it is in a valid format:

...
AND   expiration::timestamptz > now() 

If you have invalid strings like 'No End Date' in that text column, you need to clean the source or treat those specially with a CASE statement like

...
AND   CASE WHEN expiration::text = 'No End Date' THEN 'infinity'::timestamp
           WHEN expiration::text = 'foo' THEN '-infinity'::timestamp
           ELSE expiration::timestamp
      END > now()

More about the special value infinity in the manual.

The cast to text (::text) is redundant with text, but makes the expression work with date / timestamp values as well.

Use the AT TIME ZONE construct if expiration is supposed to be a local timestamp of another time zone:

expiration::timestamp AT TIME ZONE '+3'

If expiration is in ambiguous / non-standard format, use to_timestamp():

to_timestamp(expiration::text, 'yyyy-mm-dd')
share|improve this answer
    
I get the following error with your query: [Err] ERROR: operator does not exist: text > timestamp with time zone. This was my original query on all databases until I ran into this original error when querying this one machine with expiration stored as a timestamp+timezone. I'm also querying a view, does the view convert datatypes to text or something? –  Chris Apr 4 '12 at 12:20
    
@Chris: I added a bit to my answer. –  Erwin Brandstetter Apr 4 '12 at 15:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.