Selected a DISTINCT year and month issue

[darkwarrior]

Hello again, I think this is another issue with me using Unixtime which I'm starting to regret.

I would like to select a distinct year and month from my list of file attachments, shown nyah:

Untitled-1.jpg

These dates I believe all fall between November 4 and 5, 2011.

I have tried:

SELECT DISTINCT UNIX_TIMESTAMP( YEAR( date_created ) ) AS yyear, UNIX_TIMESTAMP( MONTH( date_created ) ) AS mmonth
FROM posts
WHERE post_type =  'attachment'
ORDER BY date_created DESC 
LIMIT 0 , 30

Which returns:
yyear = 0
mmonth = 0

SELECT DISTINCT YEAR( UNIX_TIMESTAMP( date_created ) ) AS yyear, MONTH( UNIX_TIMESTAMP( date_created ) ) AS mmonth
FROM posts
WHERE post_type =  'attachment'
ORDER BY date_created DESC 
LIMIT 0 , 30

Which returns the same, and:

SELECT DISTINCT YEAR( date_created ) AS yyear, MONTH( date_created ) AS mmonth
FROM posts
WHERE post_type =  'attachment'
ORDER BY date_created DESC 
LIMIT 0 , 30

Which returns:
yyear = Null
mmont = Null

In this cast I am expecting 2011 and November so I'm obviously doing something wrong but not sure what. Any help would be appreciated.

[r937]

okay, this isn't going to work -- YEAR( date_created )

that's because date_created is a unix epoch INTEGER and not a DATE or DATETIME


and this isn't going to work either -- UNIX_TIMESTAMP( date_created )

that's because you're trying to turn a unix epoch INTEGER into a unix epoch INTEGER


want a hint as to which function you should be using?

okay, here: FROM_UNIXTIME

that will turn it into a DATETIME value, and then you can use YEAR and MONTH on it

[darkwarrior]

Thanks r937, worked a treat, did this:

SELECT DISTINCT YEAR( FROM_UNIXTIME( date_created ) ) AS yyear, MONTH( FROM_UNIXTIME( date_created ) ) AS mmonth
FROM posts
WHERE post_type =  'attachment'
ORDER BY date_created DESC 
LIMIT 0 , 30

And got:
Year: 2011
Month: Nov

I try searching before I ask here but it seems difficult to find sql Operations that use unixtime, seems more common for them to use the full date system. Thank you very much again for the advice.

[r937]

you can also do it this way --

SELECT DISTINCT FROM_UNIXTIME(date_created,'%Y %b') AS yyyymon ...

but that gives one result column instead of two

[darkwarrior]

Thanks I wilal keep that in mind as getting it to that figure is part of the process after I retrieve the data. At the moment I'm more concerned with just making it all work :P