SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selected a DISTINCT year and month issue

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

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

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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937, worked a treat, did this:

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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you can also do it this way --
    Code:
    SELECT DISTINCT FROM_UNIXTIME(date_created,'%Y %b') AS yyyymon ...
    but that gives one result column instead of two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •