public inbox for [email protected]help / color / mirror / Atom feed
Where condition doesn't work as expected 4+ messages / 4 participants [nested] [flat]
* Where condition doesn't work as expected @ 2020-02-08 22:39 William Colls <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: William Colls @ 2020-02-08 22:39 UTC (permalink / raw) To: [email protected] 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. Thanks for your time. William. ^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Where condition doesn't work as expected @ 2020-02-08 22:43 Ken Benson <[email protected]> parent: William Colls <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Ken Benson @ 2020-02-08 22:43 UTC (permalink / raw) To: [email protected] <[email protected]> Ken Benson | ken @ infowerks-dot-com -----Original Message----- From: William Colls <[email protected]> Sent: Saturday, February 8, 2020 2:40 PM To: [email protected] Subject: Where condition doesn't work as expected >>>>-----Original Message----- >>>>From: William Colls <[email protected]> >>>>Sent: Saturday, February 8, 2020 2:40 PM >>>>To: [email protected] >>>>Subject: Where condition doesn't work as expected >>>> >>>>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. >>>> [Ken.B] SELECT to_date('20170103','YYYYMMDD'); https://www.postgresqltutorial.com/postgresql-to_date/ >>>>Thanks for your time. >>>> >>>>William. >>>> 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. [Ken.B] SELECT to_date('20170103','YYYYMMDD'); https://www.postgresqltutorial.com/postgresql-to_date/ Thanks for your time. William. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Where condition doesn't work as expected @ 2020-02-08 23:18 Bzzzz <[email protected]> parent: William Colls <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: Bzzzz @ 2020-02-08 23:18 UTC (permalink / raw) To: William Colls <[email protected]>; +Cc: [email protected] On Sat, 8 Feb 2020 17:39:32 -0500 William Colls <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Where condition doesn't work as expected @ 2020-02-10 15:45 Stephen Froehlich <[email protected]> parent: Bzzzz <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Stephen Froehlich @ 2020-02-10 15:45 UTC (permalink / raw) To: Bzzzz <[email protected]>; William Colls <[email protected]>; +Cc: [email protected] <[email protected]> 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 (CREATE TABLE [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 of the day its more flexible and robust. I always use it because it better matches the R POSIXct data type, which is what I always end up using on the client end. However, be prepared for time zones to drive you a little batty as different clients will default to different time zones for a given connection. --Stephen -----Original Message----- From: Bzzzz <[email protected]> Sent: Saturday, February 8, 2020 4:19 PM To: William Colls <[email protected]> Cc: [email protected] Subject: Re: Where condition doesn't work as expected On Sat, 8 Feb 2020 17:39:32 -0500 William Colls <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-02-10 15:45 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-02-08 22:39 Where condition doesn't work as expected William Colls <[email protected]> 2020-02-08 22:43 ` Ken Benson <[email protected]> 2020-02-08 23:18 ` Bzzzz <[email protected]> 2020-02-10 15:45 ` Stephen Froehlich <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox