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