Which date functions should I know, and how do the engines differ?
There are three groups of them, and the engines differ a great deal in the third group.
The first group is the portable basics, and they work the same way everywhere. The function CURRENT_DATE gives you today's date, the function CURRENT_TIMESTAMP gives you the date and the time, and the function NOW does the same thing under a different name. The function EXTRACT takes a piece out of a date and it is in the standard, so it works in PostgreSQL, MySQL and SQLite.
portable:
CURRENT_DATE today's date
CURRENT_TIMESTAMP today, with the time
EXTRACT(YEAR FROM d) the year, as a number
EXTRACT(MONTH FROM d) the month, as a number
date + 1 one day later, in PostgreSQL
The second group is the arithmetic, and that is where the engines really part company. Adding a day to a date is written d + INTERVAL '1 day' in PostgreSQL, DATE_ADD(d, INTERVAL 1 DAY) in MySQL, and date(d, '+1 day') in SQLite. Finding the difference between two dates is AGE(a, b) in PostgreSQL, DATEDIFF(a, b) in MySQL, and JULIANDAY(b) - JULIANDAY(a) in SQLite.
The third group is the rounding and the formatting, and it is the most engine specific of all. Truncating a timestamp to the start of the month is DATE_TRUNC('month', d) in PostgreSQL, DATE_FORMAT(d, '%Y-%m-01') in MySQL, and strftime('%Y-%m-01', d) in SQLite.
So what should you do about that? Two rules.
The first one is that you should never build a date or a formatted string inside your application code. If your program builds the text '2026-04-01' and passes it to the database as a string, then you have taken on the job of knowing the format, and you will get it wrong when somebody writes the date in a different shape.
The second one is that if you have to group by a month or by a day often, then store the value that you group by as its own column. A column called order_month that holds the first day of the month turns the grouping into an ordinary comparison on a real column, which is both faster and the same on every engine.
The next question in this topic puts that advice to work.