pg timestamp 时间查询问题

建表

CREATE TABLE "test"."timestamp_demo" (
"ts" timestamp(6),
"tstz" timestamptz(6)
)

 

select * From timestamp_demo

---------------------------       ---------------------------
2016-06-22 19:10:25    2016-06-23 10:10:25+08
2020-03-03 10:20:53    

 

 

select  *from timestamp_demo  where ts <=to_date('2020-03-03 11:20:53','yyyy-MM-dd hh24:mi:ss')

---------------------------     -----------------------------------
2016-06-22 19:10:25    2016-06-23 10:10:25+08

select  *from timestamp_demo  where ts <='2020-03-03 11:20:53'::timestamp

----------------------------- --------------------------------------
2016-06-22 19:10:25    2016-06-23 10:10:25+08
2020-03-03 10:20:53    

select  *from timestamp_demo  where ts <=TO_TIMESTAMP('2020-03-03 11:20:53','yyyy-MM-dd hh24:mi:ss')
----------------------------- --------------------------------------
2016-06-22 19:10:25    2016-06-23 10:10:25+08
2020-03-03 10:20:53  

 

我发现使用to_date( 函数识别不到时分秒，但是又结果又是t

 select to_date('2020-03-03 10:20:53','yyyy-MM-dd hh24:mi:ss') <= to_date('2020-03-03 17:07:00','yyyy-MM-dd hh24:mi:ss')

---

t

 select '2020-03-03 10:20:53'::timestamp <= to_date('2020-03-03 17:07:00','yyyy-MM-dd hh24:mi:ss')
-----

f

 

结论：类型转换一定要正确