MongoDB aggregation by multiple fields

MongoDB offers aggregations operations which process data records and return computed results. Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result. MongoDB provides three ways to perform aggregation: the aggregation pipeline, the map-reduce function, single purpose aggregation methods. Today, let’s add ...

PostgreSQL yesterday, today, tomorrow, now

PostgreSQL example – yesterday, today, tomorrow, now select TIMESTAMP ‘yesterday’; 2016-05-05 00:00:00 select DATE ‘yesterday’; 2016-05-05 select TIME ‘allballs’; 00:00:00 You can use similar convenient method for today and tomorrow: select TIMESTAMP ‘today’, DATE ‘today’; select TIMESTAMP ‘tomorrow’, DATE ‘tomorrow’; select now(), TIMESTAMP ‘now’, DATE ‘now’; BTW: what is ‘allballs’? It’s used to denote midnight UTC, someone ...

PostgreSQL execution plan explained

PostgreSQL execution plan explained
Recently I ran into slow query problem in our PostgreSQL database, after doing some research, I found those tips are pretty good. Just shared here: 1. Run “Explain [analyze] your SQL statement” The ANALYZE option causes the statement to be actually executed, not only planned. For more details, check PostgreSQL official ‘explain’ document: http://www.postgresql.org/docs/current/static/sql-explain.html 2. Look for “seq scan” in ...

Pagination SQL query in Oracle

Let say you have 10,000 records in your table (say NEWS). Your web front end only wants to display the first 10 news (sorted by publish_time). SELECT * FROM NEWS WHERE ROWNUM <= 10 and ROWNUM > 0 order by publish_time However, this doesn’t work.