date_trunc PostgreSQL function equal for mySQL

Question

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

Gordon Linoff · Accepted Answer · 2015-02-06 13:15:05Z

3

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.)

edited Feb 6, 2015 at 13:15

answered Feb 6, 2015 at 12:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

pozs Over a year ago

cast() would make it even ansi compatible.

Gordon Linoff Over a year ago

@pozs . . . cast() does work in MySQL, but it doesn't work in all databases. Notably, the Oracle date type includes a time component.

pozs Over a year ago

@GordonLinoff i wrote ansi sql compatible, not Oracle compatible. There is a difference :) (ansi sql explicitly mentions a cast from timestamp to date & date hasn't got time part in ansi)

Gordon Linoff Over a year ago

@pozs . . . You made me wonder why I only use that formulation in SQL Server (where the alternative is really yucky). But, that is a fine solution as well.

Collectives™ on Stack Overflow

date_trunc PostgreSQL function equal for mySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related