CODEWITHSUNDEEP
mysqlpostgresql
Michael Kampmann Rasmussen
Michael Kampmann Rasmussen

Reputation: 148

date_trunc PostgreSQL function equal for mySQL

Im trying to retrieve data to make statistics, im using mySQL and i cant get the following function to work - the postgreSQL is working.

I want to retrieve the request for the last month and count the amount of new requests for each day.

postgreSQL

SELECT count(*), date_trunc('day', created_at) as date FROM requests
WHERE(created_at > '2014-08-13 00:00:00') GROUP BY 2 ORDER BY 2 ASC;

*mySQL - my code *

SELECT count(EXTRACT(DAY FROM created_at)), EXTRACT(DAY FROM created_at) as date
FROM `requests`
WHERE EXTRACT(DAY FROM NOW() - INTERVAL 1 MONTH)
GROUP BY date

Final code

SELECT count( * ) , date( created_at ) AS date
FROM `requests`
WHERE DATE( created_at ) > DATE( DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) )
GROUP BY date

Upvotes: 1

Views: 3678

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

The equivalent for your case is date():

select date(created_at), count(*)
from requests
. . .

This isn't a general replacement, but it works to remove the time portion of a date.

EDIT:

Perhaps the better solution for these two databases is:

select cast(created_at as date)

This is ANSI standard and works in both these databases (as well as SQL Server). I personally don't use this in general, lest I accidentally use it in Oracle, causing difficult to find errors. (dates in Oracle have a time component, alas.)

Upvotes: 3

Related Questions