To add some context to Bzzz's statement, read this page:
https://www.postgresql.org/docs/current/datatype-datetime.html
If you were building this table from scratch it should be a timestamp to timestamptz. ... if you can rebuild the table
(CREATETABLE [new_name] AS SELECT ...) https://www.postgresql.org/docs/current/sql-createtableas.html you probably
should.
- timestamp is for when you know its UTC
- timestamp with time zone / timestamptz can trip you up with environment timezones on the client end, but at the end
ofthe day its more flexible and robust. I always use it because it better matches the R POSIXct data type, which is
whatI always end up using on the client end. However, be prepared for time zones to drive you a little batty as
differentclients will default to different time zones for a given connection.
--Stephen
-----Original Message-----
From: Bzzzz <lazyvirus@gmx.com>
Sent: Saturday, February 8, 2020 4:19 PM
To: William Colls <william@williamcollsassoc.ca>
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: Where condition doesn't work as expected
On Sat, 8 Feb 2020 17:39:32 -0500
William Colls <william@williamcollsassoc.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
convertingit to a TIMESTAMP or TIMESTAMPTZ type to avoid any conversion when querying.
Jean-Yves