CODEWITHSUNDEEP
mysqlsql
Orionis
Orionis

Reputation: 1011

Again on SQL dates conversion

I read a zilion of links on this matter, but the more I read the more I'm confused.
In my DB I have a date column, with 'YYYY-MM-DD' format. I need to count the occorrences of month+year change, regardless of days. I currently use: 

$query = "SELECT COUNT(data) as visits, COUNT(DISTINCT data) as diffentDates ,MIN(data) as 'MIN', MAX(data) as 'MAX' FROM visiting ";

Where 'data' is the value in my column.
This works fine: I get the totals for visits, the number of different dates, the first and the last visit.
The problem comes with 'diffentDates'; it should only count the changes in 'YYMM'.
I tried to use something similar to: COUNT( DISTINCT ( FORMAT (data,'YYMM') ) ) as diffentDates
but it does not do what I need (actually it gives errors or selects all the dates anyway).
Which is is the correct function to apply to translate (and select and count) from 'YYYY-MM-DD' to 'YYMM' ?
Thanks

Upvotes: 0

Views: 41

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

If the column data is a TIMESTAMP or DATE type, the following should work:

SELECT 
    COUNT(`data`) as visits, 
    COUNT(DISTINCT DATE_FORMAT(`data`,'%Y%m')) as diffentDates,
    MIN(`data`) as 'MIN', 
    MAX(`data`) as 'MAX' 
FROM `visiting`

Upvotes: 1

Related Questions