Re: Where condition doesn't work as expected

On Sat, 8 Feb 2020 17:39:32 -0500
William Colls <william(at)williamcollsassoc(dot)ca> wrote:

> I have the following condition a select statement:
>
>  WHERE "datetime" > '2019-03-31 23:59:59'
>
> datetime is a character field with data in the format YYYY-MM-DD
> HH:MM:SS. However it returns any line where the datetime field begins
> with 2019. I suspect that I should be using timestamp values, but I
> can't figure out how to cast the datetime field value to a timestamp.

Depending on what you need, use either TIMESTAMP or TIMESTAMPTZ.

[…] WHERE (datetime::TIMESTAMP) > TIMESTAMP'2019-03-31 23:59:59' ;

(note that the TIMESTAMP to the right of the comparison is not
mandatory.)

As it costs, if you have no special reason for the "datetime" column to
dwell in text or varchar, you should consider converting it to a
TIMESTAMP or TIMESTAMPTZ type to avoid any conversion when querying.

Jean-Yves