DATE_FORMAT() equivalent in PostgreSQL

Question

I have this query in mysql

SELECT * 
FROM `calendar` 
WHERE DATE_FORMAT(startTime, "%Y-%m-%d") = '2010-04-29'

How can i convert to Postgresql query?

http://www.postgresql.org/docs/devel/static/functions-formatting.html — NullPoiиteя, Nov 02 '12 at 06:26

score 4 · Accepted Answer · edited May 23 '17 at 12:16

4

Basically, the query in MYSQL which uses DATE_FORMAT() converts date into string. If you want to compare it with date, don't use DATE_FORMAT() but instead DATE(). Try this, in PostgreSQL, casting timestamp into date,

SELECT * 
FROM   "calendar"
WHERE  "startTime"::date = '2010-04-29'

edited May 23 '17 at 12:16

Community

1
1

answered Nov 02 '12 at 06:31

John Woo

258,903
69
498
492

Somehow my query is not returning the correct results. In my database coulmn currently my date is stored as this `2012-10-05 09:00:00+08 ` when i try "startTime"::date = '2012-10-05' i am not getting any result – Mirage Nov 02 '12 at 06:56
what is the data type of `startTime` column? – John Woo Nov 02 '12 at 06:57
datetime , i think i have problem somewhere in my joins , i will check that. thanks for help – Mirage Nov 02 '12 at 07:01

score 0 · Answer 2 · answered Nov 02 '12 at 06:31

0

SELECT *
FROM calendar
WHERE starttime::date = '2010-04-29'

answered Nov 02 '12 at 06:31

mvp

111,019
13
122
148

DATE_FORMAT() equivalent in PostgreSQL

2 Answers2