Getting Count of Elements by 'Created_At' by Day in a Given Month

Getting count of elements by `created_at` by day in a given month

I think separation trumps the minimal performance gains here:

# Controller
@users = User.all(:conditions => ["created_at >= ?", Date.today.at_beginning_of_month])

# View
Date.today.at_beginning_of_month.upto(Date.today).each do |date|
  <%= date %>: <%= @users.select{|u| u.created_at == date }.size %>
end

Mysql - Count element in month group by day

Making some assumptions on the format of your data and the tables:-

SELECT aDate, COUNT(SomeDateTable.id)
FROM
(
    SELECT DATE_ADD("2013-02-10", INTERVAL (Hundreds.i * 100 + Tens.i * 10 + Units.i) DAY) AS aDate
    FROM
    (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units,
    (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens,
    (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Hundreds
    WHERE DATE_ADD("2013-02-10", INTERVAL (Hundreds.i * 100 + Tens.i * 10 + Units.i) DAY) <= "2013-03-10"
) sub1
INNER JOIN SomeDateTable
ON sub1.aDate BETWEEN DATE(SomeDateTable.start_date) AND DATE(SomeDateTable.end_date)
GROUP BY aDate;

This will cope with date ranges of up to 1000 days

SQL fiddle for it here:-

http://www.sqlfiddle.com/#!2/103b7/1

Want to get count of items for every date of a given month in MongoDB

aggregate

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "created_at": "$created_at"
      },
      "pending": {
        $sum: {
          "$cond": [
            {
              "$eq": [
                "$status",
                "pending"
              ]
            },
            1,
            0
          ]
        }
      },
      "missed": {
        $sum: {
          "$cond": [
            {
              "$eq": [
                "$status",
                "missed"
              ]
            },
            1,
            0
          ]
        }
      },
      "completed": {
        $sum: {
          "$cond": [
            {
              "$eq": [
                "$status",
                "completed"
              ]
            },
            1,
            0
          ]
        }
      },
      
    }
  }
])

mongoplayground

Count the number of events on a day of the month in MySQl

Use DAY() instead of DATE():

SELECT DAY(created_at), COUNT(*)
FROM loan
WHERE created_at >= '2020-08-01' AND
      created_at < '2021-01-01'
GROUP BY DAY(created_at);

Note that you don't need all the DATE() functions in the WHERE clause. That can slow down performance if indexes could be used for the WHERE conditions.

get count of users for previous day week or month in ruby on rails

I would try something like this:

For example if you want to selecet the Users created in the last hour:

  @date_start = DateTime.now
  @date_end = @date_start - 1.hour
  @users = User.where(:created_at => @date_end..@date_start)

MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

SQL count data from beginning of the date till current month/year

If you are running MySQL 8.0, you can do a window sum. Starting from your existing query:

SELECT 
    monthNameYear,
    monthName,
    SUM(COUNT(*)) OVER(ORDER BY monthNameYear) totalPurchase
FROM (
    SELECT 
        DATE_FORMAT(`reservation_for`,'%Y-%m') monthNameYear,
        DATE_FORMAT(`reservation_for`,'%M %Y') monthName,
        email
    FROM `tablename`
    GROUP BY monthNameYear, monthName, email 
    HAVING COUNT(*) > 1 
) as sub
GROUP BY monthNameYear, monthName
ORDER BY monthNameYear;

Notes:

• always enumerate all non-aggregated columns in the group by clause

• the order by clause should be placed in the outer query

• you are selecting COUNT(*) in the subquery, but it is not used in the outer query - I removed that column from theselect` clause

Count of active items on day given start and stop date

You can do this:

df[["start_date", "stop_date"]] = df[["start_date", "stop_date"]].apply(pd.to_datetime)

df = df.ffill(axis=1)
df["days"] = [
    pd.date_range(s, e, freq="D") for s, e in zip(df["start_date"], df["stop_date"])
]

df2 = (
    df.explode("days")
    .groupby("days")["id"]
    .nunique()
    .reindex(pd.date_range(df["start_date"].min(), df["stop_date"].max()), fill_value=0)
)

Output:

2019-06-01    2
2019-06-02    2
2019-06-03    2
2019-06-04    2
2019-06-05    2
2019-06-06    0
2019-06-07    2
2019-06-08    3
2019-06-09    4
2019-06-10    2
2019-06-11    1
2019-06-12    0
2019-06-13    1
Freq: D, Name: id, dtype: int64

And, use pd.IntervalIndex:

active_date = pd.Timestamp('2019-06-10')

df[
    pd.IntervalIndex.from_arrays(df["start_date"], df["stop_date"]).contains(
        active_date
    )
].drop("days", axis=1)

Output:

    id start_date  stop_date
1  Bar 2019-06-07 2019-06-10
2  Pop 2019-06-09 2019-06-11

Count the number of days per month between two dates

Something like this? You get the days per month even for leap years like february 2020.

function getDays() {    var dropdt = new Date(document.getElementById("arr").value);    var pickdt = new Date(document.getElementById("dep").value);    var result = "";    for (var year = dropdt.getFullYear(); year <= pickdt.getFullYear(); year++) {        var firstMonth = (year == dropdt.getFullYear()) ? dropdt.getMonth() : 0;        var lastMonth = (year == pickdt.getFullYear()) ? pickdt.getMonth() : 11;        for (var month = firstMonth; month <= lastMonth; month++) {            var firstDay = (year === dropdt.getFullYear() && month === firstMonth) ? dropdt.getDate() : 1;            var lastDay = (year === pickdt.getFullYear() && month === lastMonth) ? pickdt.getDate() : 0;            var lastDateMonth = (lastDay === 0) ? (month + 1) : month            var firstDate = new Date(year, month, firstDay);            var lastDate = new Date(year, lastDateMonth, lastDay);            result += (month + 1) + " - " + parseInt((lastDate - firstDate) / (24 * 3600 * 1000) + 1) + "; ";        }    }    return result;}
function cal() {    if (document.getElementById("dep")) {        document.getElementById("number-of-dates").value = getDays();    }}

<form action="javascript:void(0);">  <input id="arr" type="date" name="arr" value="2019-09-15">   <input id="dep" type="date" name="dep" value="2020-03-15">  <button onclick="cal()">Calculate</button>  <input style="width:400px" id="number-of-dates" type="text"></form>

ArrayListDate how to count the number of occurrences by Month/Year

java.time

You are using terrible date-time classes that were supplanted years ago by the java.time classes. Never use java.util.Date.

LocalDateTime

Use LocalDateTime class to represent your input values, a date with a time-of-day but without a time zone.

ArrayList< LocalDateTime > dataUser = …

Your input strings are close to the standard ISO 8601 formats used by default in java.time. Just replace the SPACE in the middle with a T to comply with the standard.

LocalDateTime ldt = LocalDateTime.parse( input.replace( " " , "T" ) ;

Example code:

List < String > inputs = List.of( "2020-01-01 13:00:00" , "2020-01-03 11:00:00" , "2020-01-05 17:00:00" , "2020-04-01 01:00:00" ,
        "2020-04-02 19:00:00" , "2020-04-29 23:00:00" , "2018-01-01 14:00:00" , "2018-01-04 05:00:00" );
System.out.println( "inputs = " + inputs );

List < LocalDateTime > ldts = new ArrayList <>( inputs.size() );
for ( String input : inputs )
{
    try
    {
        LocalDateTime ldt = LocalDateTime.parse( input.replace( " " , "T" ) );
        Objects.requireNonNull( ldt );
        ldts.add( ldt );
    }
    catch ( Exception e )
    {
        // Faulty input.
        e.printStackTrace();
    }
}
System.out.println( "ldts = " + ldts );

When run.

inputs = [2020-01-01 13:00:00, 2020-01-03 11:00:00, 2020-01-05 17:00:00, 2020-04-01 01:00:00, 2020-04-02 19:00:00, 2020-04-29 23:00:00, 2018-01-01 14:00:00, 2018-01-04 05:00:00]
ldts = [2020-01-01T13:00, 2020-01-03T11:00, 2020-01-05T17:00, 2020-04-01T01:00, 2020-04-02T19:00, 2020-04-29T23:00, 2018-01-01T14:00, 2018-01-04T05:00]

YearMonth

From those LocalDate objects, get a YearMonth object that represents, well, the year and the month only, without the day-of-month.

YearMonth yearMonth = YearMonth.from( ldt ) ;

Group by year-month

Create a Map< YearMonth , List< LocalDate > > to segregate the date objects by year-month. Then interrogate the list for its size to get your desired count.

Map < YearMonth, List < LocalDateTime > > mapYearMonthToLdts = new TreeMap <>();

Loop through all your LocalDateTime, determine each one’s year-month. Look in the map to see if there is a list already assigned for that year-month. If not yet assigned a list, make a list, and assign it to the map. Lastly, add the LocalDateTime object to that list whether pre-existing or newly created.

for ( LocalDateTime ldt : ldts )
{
    List < LocalDateTime > listOfLdts = mapYearMonthToLdts.get( YearMonth.from( ldt ) );
    if ( listOfLdts == null )
    {
        listOfLdts = new ArrayList <>();
        mapYearMonthToLdts.put( YearMonth.from( ldt ) , listOfLdts );
    }
    listOfLdts.add( ldt );
}
System.out.println( "mapYearMonthToLdts = " + mapYearMonthToLdts );

When run.

mapYearMonthToLdts = {2018-01=[2018-01-01T14:00, 2018-01-04T05:00], 2020-01=[2020-01-01T13:00, 2020-01-03T11:00, 2020-01-05T17:00], 2020-04=[2020-04-01T01:00, 2020-04-02T19:00, 2020-04-29T23:00]}

Multimap

The map-related code above is old-school. Modern Java makes this much simpler.

Our map is actually a multimap, where the key leads to a collection of values rather than a single value. Modern Java provides multimap syntax via Map::computeIfAbsent method added to Java 8 and later.

for ( LocalDateTime ldt : ldts )
{
    mapYearMonthToLdts
            .computeIfAbsent( YearMonth.from( ldt ) , ( x -> new ArrayList <>() ) )  // Retrieves the value (a list) matching the key, after creating one if none found to be pre-existing.
            .add( ldt );  // Add this value to the retrieved/created list.
}
System.out.println( "mapYearMonthToLdts = " + mapYearMonthToLdts );

Output remains the same. This new-school code is not better, just shorter, and easier to comprehend when reviewing code later. If you are not yet comfortable with lambda syntax, use the old-school code seen earlier.

Report results

You wanted a count of elements per year-month.

We can build a report by looping the keys, looping the YearMonth objects. For each year-month key, we retrieve its List of LocalDateTime. We interrogate that list for its size.

for ( YearMonth yearMonth : mapYearMonthToLdts.keySet() )
{
    System.out.println( "yearMonth = " + yearMonth + " has a count of: " + mapYearMonthToLdts.get( yearMonth ).size() );
}

When run.

yearMonth = 2018-01 has a count of: 2
yearMonth = 2020-01 has a count of: 3
yearMonth = 2020-04 has a count of: 3

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
  
  • Java 9 brought some minor features and fixes.
• Java SE 6 and Java SE 7
  
  • Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  
  • Later versions of Android (26+) bundle implementations of the java.time classes.
  • For earlier Android (<26), a process known as API desugaring brings a subset of the java.time functionality not originally built into Android.
    
    • If the desugaring does not offer what you need, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above) to Android. See How to use ThreeTenABP….

---

Related Topics