belsrc

db.getCollection('collection').aggregate([ 
    { $match: { $and: [
            { created_at: { $exists: true } },
            { created_at: { $ne: null } }
        ] } },
    { $project: { 
        month: { $month: "$created_at" }, 
        year: { $year: "$created_at" }, 
        day: { $dayOfMonth: "$created_at" } } },
    { $group: { _id: { "year": "$year", "month": "$month", "day": "$day" } } },

belsrc

<template>
</template>

<script>
import { mapState, mapGetters, mapActions } from 'vuex';
import * as types from './../store/action_types';

export default {
  components: { },

belsrc

/*
db.setProfilingLevel(2)     Needs to be ran first
  0 - Off
  1 - Only slow
  2 - all
  
This enables query profiling, after enabling profiling, run the application for a bit to get some data.
*/
db.getCollection('system.profile')
  .find({

belsrc

-- Line 9 equals indexes

SELECT nspname || '.' || relname AS "relation",
       pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind = 'i'
  AND nspname !~ '^pg_toast'

belsrc

-- Line 9 ignores indexes

SELECT nspname || '.' || relname AS "relation",
       pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind != 'i'
  AND nspname !~ '^pg_toast'

belsrc

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL

belsrc

-- Relations, as in, tables and indexes

SELECT nspname || '.' || relname AS "relation",
       pg_size_pretty(pg_relation_size(PC.oid)) AS "size"
FROM pg_class PC
LEFT JOIN pg_namespace NS ON (NS.oid = PC.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') 
  AND nspname !~ '^pg_toast'
ORDER BY pg_relation_size(PC.oid) DESC

belsrc

-- Get a count of people in each city from a specific state

SELECT DISTINCT(city) AS city, COUNT(city) AS total 
FROM data
WHERE state = ?
GROUP BY city
ORDER BY city ASC;

belsrc

-- Get a count of, lets say, people's names that start with each letter
-- from a specific city and state

SELECT LEFT(name, 1) AS letter, COUNT(id) AS total 
FROM data_table 
WHERE city = ? AND state = ?
GROUP BY letter 
ORDER BY letter ASC;

belsrc

SELECT table_schema AS 'Database name',
	SUM(data_length + index_length) / 1024 / 1024 AS 'Size (MB)',
	SUM(data_free) / 1024 / 1024 AS 'Free (MB)'
FROM information_schema.TABLES
GROUP BY table_schema