Querying for timestamp Postgresql

Question

I am trying to perform a query which contains a date through an API. The date should be in a ISO_8601 date format(yyyy-MM-dd'T'HH:mm:ssZ).

For example, one of the tuples in my table could have the date:

2012-11-11T00:00:00+0000.

In unicode it is queried for as follows:

2012-11-11T00%3A00%3A00%2B0000

All my own checks to see if the date is valid passes but I am getting the following SQL Exception:

SQL Exception: ERROR: operator does not exist: timestamp with time zone >= character varying

My code which carries out the queries through the API is written in python. I'm not sure what the exception means? Is it not possible to query for a timestamp with a String?

Could you post some actual code please? :)

Talvalin
– Talvalin

2012-11-27 09:06:50 +00:00
Commented Nov 27, 2012 at 9:06 — Talvalin
– Talvalin, Commented Nov 27, 2012 at 9:06

user330315 · Accepted Answer · 2012-11-27 09:10:58Z

2

To specify a timestamp literal, use the following format:

where ts_column >= timestamp '2012-11-11 T00:00:00+0000'

The keyword timestamp is important to define a timestamp literal, otherwise it's "only" a character value (and that's what the error message tells you). The value must conform to the ISO date style.

Another option would be to use the to_timestamp function:

where ts_column >= to_timestamp('2012-11-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss');

which gives you more control over the actual format of your literal.

For more details see the manual: http://www.postgresql.org/docs/current/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE

answered Nov 27, 2012 at 9:10

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying for timestamp Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related