SQL Query to Get Data for the Last 1 Year: MySQL, PostgreSQL and SQL Server, Without Killing the Index

Q: Why is my date range query slow even though the column is indexed?

Almost always because the column is wrapped in a function such as YEAR(created_at) or DATE(created_at), which stops the index from being used. Rewrite the condition so the column stands alone on one side and all the date arithmetic is on the other, then confirm with EXPLAIN that an index range scan is used.

Q: Should I use BETWEEN for a date range in SQL?

Avoid it for datetime columns. BETWEEN is inclusive, so an end date of December 31st drops rows with a time after midnight on that day. Use >= for the start and < for the start of the next period, which has no gap and works for dates, datetimes and timestamps alike.

Q: Why does my last-year query return different rows on the server?

Time zones. NOW() and GETDATE() return the session's local time, and the server's session is often UTC while yours is not, so the two sessions disagree about where the year begins. Store timestamps in UTC, keep sessions in UTC, and convert only for display.

To get the last year of data in SQL, compare the date column against today minus one year: WHERE created_at >= NOW() - INTERVAL 1 YEAR in MySQL, WHERE created_at >= now() - interval ‘1 year’ in PostgreSQL, and WHERE created_at >= DATEADD(year, -1, GETDATE()) in SQL Server. Keep the arithmetic on the right-hand side, never wrap the column in a function, and decide first whether you mean the last twelve months or the previous calendar year.

The top search results answer this for one database and stop. The version that survives contact with a production table has three more parts: the dialect you are actually on, whether the index still gets used, and what happens at midnight in a time zone you did not think about.

The rolling-year query in each dialect
Rolling year or calendar year: decide which one you mean
Keep the function off the column
The time zone trap
Grouping the year by month, and the missing-month problem
Where the query runs
How this fits the rest of the stack
FAQ

The rolling-year query in each dialect

Rolling year means from this moment, back 365 or 366 days. It is the most common meaning and the easiest to write.

-- MySQL / MariaDB
SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL 1 YEAR;

-- PostgreSQL
SELECT * FROM orders
WHERE created_at >= now() - interval '1 year';

-- SQL Server
SELECT * FROM orders
WHERE created_at >= DATEADD(year, -1, GETDATE());

Three notes that save a support ticket. MySQL also accepts DATE_SUB(NOW(), INTERVAL 1 YEAR), which is the same thing spelled longer. In PostgreSQL, now() is the transaction start time, so a long transaction sees a fixed value, which is usually what you want. In SQL Server, if the column is a plain date rather than datetime, compare against CAST(GETDATE() AS date) so you do not include a partial day.

If the column is a date without a time component, >= is exactly right. If it carries a time, the row from exactly one year ago at 09:00 is included when you run at 09:01 and excluded at 08:59, which is what rolling means. If that bothers you, you want the calendar version.

Rolling year or calendar year: decide which one you mean

The previous calendar year is a different question, and reporting usually wants it: all of last year, January to December, regardless of today’s date. The tempting form is YEAR(created_at) = YEAR(CURDATE()) - 1, and it is the one to avoid, for reasons in the next section. Write it as a range instead.

-- MySQL: previous calendar year
SELECT * FROM orders
WHERE created_at >= MAKEDATE(YEAR(CURDATE()) - 1, 1)
  AND created_at <  MAKEDATE(YEAR(CURDATE()), 1);

-- PostgreSQL: previous calendar year
SELECT * FROM orders
WHERE created_at >= date_trunc('year', now()) - interval '1 year'
  AND created_at <  date_trunc('year', now());

-- SQL Server: previous calendar year
SELECT * FROM orders
WHERE created_at >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1)
  AND created_at <  DATEFROMPARTS(YEAR(GETDATE()), 1, 1);

The pattern is always the same: greater than or equal to the start, strictly less than the start of the next period. Never BETWEEN with an end date of December 31st, because a datetime column will have rows at 23:59:59.5 on the 31st that BETWEEN silently drops. The half-open range is the habit that prevents the whole class of off-by-a-day bugs.

Year to date is the same shape with now() as the upper bound, and the last twelve whole months is date_trunc(‘month’, now()) - interval ‘12 months’ up to date_trunc(‘month’, now()). Once the range form is in your fingers, every variant is a two-line change.

Keep the function off the column

This is the part that separates a query that returns in milliseconds from one that scans forty million rows. An index on created_at stores the raw values in order. The database can use it when the condition compares the column itself against a value: created_at >= some_date. It cannot use it when the column is wrapped in a function, because YEAR(created_at) is a computed value the index does not contain.

-- Uses the index on created_at
WHERE created_at >= now() - interval '1 year'

-- Cannot use the index: full scan
WHERE EXTRACT(year FROM created_at) = 2025
WHERE DATE(created_at) >= '2025-01-01'
WHERE YEAR(created_at) = YEAR(CURDATE()) - 1

The property is called sargability, and the rule of thumb is: all the arithmetic goes on the side without the column. Run EXPLAIN on the query before and after. On PostgreSQL you want an Index Scan or Index Range Scan on created_at; on MySQL you want type: range with the index named in key. If you see Seq Scan or type: ALL on a table with an index, the function is the reason.

And if there is no index on the date column, the query is a full scan whatever you write. For any table you filter by date more than once a day, the index is the fix, and it is usually the first one worth adding.

The time zone trap

NOW() returns the current time in the session’s time zone, and the session’s time zone is whatever the connection or the server was configured with, which is frequently not what the application thinks. The result is a report that runs correctly from the office and returns different rows from a cron job on the server, because the cron job’s session is in UTC and the office’s is not.

The stable arrangement is: store timestamps in UTC, keep the session in UTC, and convert for display at the edge. In PostgreSQL, use timestamptz rather than timestamp for the column, so the value carries its offset and comparisons are unambiguous. In MySQL, set time_zone to +00:00 on the connection and treat the DATETIME column as UTC by convention. In SQL Server, prefer SYSUTCDATETIME() over GETDATE() when the column is stored in UTC.

The bug shows up at period boundaries. A calendar year that starts at midnight local time starts at a different UTC instant depending on the zone, and the rows in that gap either appear in two years or in none. If the report and the database disagree about what midnight is, agree on UTC first and argue about display later.

Grouping the year by month, and the missing-month problem

Most last-year queries are really a chart, and the chart needs one row per month.

-- PostgreSQL
SELECT date_trunc('month', created_at) AS month, COUNT(*) AS orders
FROM orders
WHERE created_at >= date_trunc('month', now()) - interval '12 months'
  AND created_at <  date_trunc('month', now())
GROUP BY 1 ORDER BY 1;

-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month, COUNT(*) AS orders
FROM orders
WHERE created_at >= DATE_FORMAT(CURDATE() - INTERVAL 12 MONTH, '%Y-%m-01')
  AND created_at <  DATE_FORMAT(CURDATE(), '%Y-%m-01')
GROUP BY 1 ORDER BY 1;

Note that the function in the SELECT and GROUP BY is fine. Sargability is about the WHERE clause; the rows have already been selected by index before the grouping runs.

The remaining problem is a month with no rows, which simply does not appear, and a chart with eleven bars where the twelfth is missing rather than zero. In PostgreSQL, generate_series produces the twelve month starts and a LEFT JOIN fills the gaps with zero. In MySQL there is no generate_series, so the usual fix is a small calendar table with one row per month, which is also useful for every other report you will write.

Where the query runs

A reporting query that scans a year of a large table competes with the application for the same CPU, memory and connections. On a small instance, the report is the thing that makes checkout slow at the end of the month. The fixes are the ones above, the index first of all, plus running reports off-peak and keeping them off the connection pool the application uses.

On RunxBuild a managed MySQL or Postgres database has a documented connection limit per plan, backups, user management so the report can run as a read-only user, and a private network to the services beside it. The plan ladder runs from the $4 Dev plan through the $13 BasicMini with 1GB of memory to the $20 BasicPlus with 2GB, and memory is the number that decides whether a year of data fits in cache or comes off disk every time the report runs.

How this fits the rest of the stack

A year of data is a memory question as much as a SQL question, and memory is a plan size. The RunxBuild hosting calculator shows the database plan next to the service, the storage and the bandwidth, so the cost of a report that runs quickly is a line item you can see rather than a slow checkout you find out about.

Useful related references:

FAQ

How do I get the last 12 months of data in SQL?

Compare the date column against today minus one year, with the arithmetic on the value side: created_at >= NOW() - INTERVAL 1 YEAR in MySQL, created_at >= now() - interval ‘1 year’ in PostgreSQL, created_at >= DATEADD(year, -1, GETDATE()) in SQL Server. Never wrap the column in a function or the index will not be used.

How do I select the previous calendar year in SQL?

Use a half-open range from January 1st of last year up to but not including January 1st of this year. In PostgreSQL that is created_at >= date_trunc(‘year’, now()) - interval ‘1 year’ AND created_at < date_trunc(‘year’, now()). MySQL uses MAKEDATE and SQL Server uses DATEFROMPARTS for the same two boundaries.

Why is my date range query slow even though the column is indexed?