Professional Web Applications Themes

GROUP BY creation date - MySQL

I have a table structure which includes a UNIX timestamp (seconds since '1970-01-01 00:00:00' UTC) for the creation date. I would like to return a record count grouped by creation date using the time range from 00:00:00 to 23:59:59 for each respective day. For example: Date, Count 24 October, 23 23 October, 12 22 October, 16 Can this one done in one query using only SQL?...

  1. #1

    Default GROUP BY creation date

    I have a table structure which includes a UNIX timestamp (seconds since
    '1970-01-01 00:00:00' UTC) for the creation date.

    I would like to return a record count grouped by creation date using the
    time range from 00:00:00 to 23:59:59 for each respective day.

    For example:

    Date, Count

    24 October, 23
    23 October, 12
    22 October, 16

    Can this one done in one query using only SQL?


    Bosconian Guest

  2. #2

    Default Re: GROUP BY creation date

    On Tue, 24 Oct 2006 22:11:53 -0700, Bosconian wrote: 

    Yes.

    See http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
    paying special attention to the FROM_UNIXTIME() function, with the
    option that allows specifying formats that only include the information
    you want.

    --
    28. My pet monster will be kept in a secure cage from which it cannot escape
    and into which I could not accidentally stumble.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  3. #3

    Default Re: GROUP BY creation date


    Bosconian wrote: 

    Untested, and just a guess:

    SELECT DATE( FROM_UNIXTIME( field ) ) AS creation_date,
    COUNT(DATE(FROM_UNIXTIME(field)) AS cnt
    FROM table
    GROUP BY creation_date

    strawberry Guest

  4. #4

    Default Re: GROUP BY creation date

    "strawberry" <com> wrote in message
    news:googlegroups.com... 
    >
    > Untested, and just a guess:
    >
    > SELECT DATE( FROM_UNIXTIME( field ) ) AS creation_date,
    > COUNT(DATE(FROM_UNIXTIME(field)) AS cnt
    > FROM table
    > GROUP BY creation_date
    >[/ref]

    Cool, this looks like it works. I just needed to add a third parenthesis
    after the COUNT clause. Thanks!


    Bosconian Guest

Similar Threads

  1. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  2. Adding IPTC data, keywords, changes file creation date
    By Tina_Garfield@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 3
    Last Post: February 26th, 07:04 PM
  3. Import creation date on Macintosh
    By MW in forum FileMaker
    Replies: 2
    Last Post: August 14th, 08:53 PM
  4. SQL Server Group By Total not Date
    By Anthony in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 01:40 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139