How to Use Unix Timestamps for Date Range Queries

Querying records within a date range — "show me everything from the last 30 days," "find orders between January 1 and March 31" — is one of the most common operations in any database-backed application. How you store and query timestamps has a significant impact on how simple and reliable those queries are.

Unix timestamps (integers representing seconds since the Unix epoch) are often the best choice for this, but only if you understand how to use them correctly. Here's the practical guide.

Why Unix Timestamps Make Range Queries Simpler

When timestamps are stored as integers, a date range query becomes a simple numeric comparison:

SELECT * FROM events
WHERE created_at >= 1704067200
  AND created_at < 1706745600;

That's it. No string parsing, no timezone conversion, no BETWEEN semantics to worry about. The database compares two integers.

Compare this to a formatted datetime string approach:

SELECT * FROM events
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at < '2024-02-01 00:00:00';

This looks similar, but introduces several questions: What timezone is '2024-01-01 00:00:00' in? Does your application server interpret it the same way as your database server? What happens when users in different timezones run the same query?

Unix timestamps sidestep all of this. They're always UTC, always unambiguous, always a number. The conversion to local time happens at the display layer, not in the query.

Calculating the Timestamps for Common Date Ranges

To write a range query, you need the Unix timestamps for the start and end of the range. The Unix timestamp converter makes this easy — enter the date and get the timestamp.

Here are the calculations for common query patterns:

Last N days: ` start = current_timestamp - (N × 86400) end = current_timestamp `

Last 7 days: start = now - 604800 (7 × 86,400 seconds) Last 30 days: start = now - 2592000 (30 × 86,400) Last 90 days: start = now - 7776000

A specific calendar month (e.g., March 2024): ` start = Unix timestamp of 2024-03-01 00:00:00 UTC = 1709251200 end = Unix timestamp of 2024-04-01 00:00:00 UTC = 1711929600 `

Query: WHERE created_at >= 1709251200 AND created_at < 1711929600

Note: use < (less than) rather than <= for the end boundary when using midnight of the next day. This cleanly excludes any records from April 1 without worrying about the exact last second of March 31.

Year to date: ` start = Unix timestamp of January 1 of current year, 00:00:00 UTC end = current timestamp `

Fixed date range between two dates: Convert both dates to UTC midnight timestamps using the converter and use them directly as boundaries.

Handling Timezones Correctly

The most common mistake with Unix timestamp range queries is timezone confusion when generating the boundary timestamps.

Unix timestamps are always UTC. If your application needs to query "orders placed on April 8 in New York time (UTC-4)," then April 8 in New York spans:

2024-04-08 00:00:00 EDT = 2024-04-08 04:00:00 UTC = Unix timestamp 1712545200
2024-04-09 00:00:00 EDT = 2024-04-09 04:00:00 UTC = Unix timestamp 1712631600

If you naively use midnight UTC instead:

2024-04-08 00:00:00 UTC = Unix timestamp 1712534400

You'd be including 4 hours of records from late April 7 New York time, and missing 4 hours of records from late April 8.

The correct approach: always convert your date range to UTC before generating the boundary timestamps. In code:

import datetime, pytz

tz = pytz.timezone('America/New_York')
start_local = tz.localize(datetime.datetime(2024, 4, 8, 0, 0, 0))
start_utc_ts = int(start_local.utctimetuple().tm_sec)  # or .timestamp()

Or use a date library that handles timezone-aware timestamps. The key principle: generate the boundaries in the user's local time, convert to UTC, then use the resulting integer in the query.

Indexing for Performance

For timestamp range queries to be fast, the timestamp column needs an index. On a table with millions of rows, an unindexed integer scan for a date range will be slow regardless of how simple the query looks.

PostgreSQL: `sql CREATE INDEX idx_events_created_at ON events (created_at); `

MySQL: `sql ALTER TABLE events ADD INDEX idx_created_at (created_at); `

For queries that frequently combine timestamp ranges with other filters (e.g., WHERE user_id = 123 AND created_at >= X), a composite index with the most selective column first is usually faster:

CREATE INDEX idx_events_user_created ON events (user_id, created_at);

Composite indexes let the database narrow down by user first, then scan only that user's time-ordered records for the range.

Generating Timestamp Boundaries in Common Languages

JavaScript: `javascript const now = Math.floor(Date.now() / 1000); const last30Days = now - (30 * 86400); // Query: WHERE created_at >= last30Days AND created_at <= now `

Python: `python import time now = int(time.time()) last_30_days = now - (30 * 86400) `

For a specific UTC date: `python import datetime dt = datetime.datetime(2024, 1, 1, tzinfo=datetime.timezone.utc) ts = int(dt.timestamp()) # 1704067200 `

PHP: `php $now = time(); $start = mktime(0, 0, 0, 1, 1, 2024); // Jan 1 2024 midnight local time // Use strtotime for UTC: strtotime('2024-01-01 00:00:00 UTC') `

SQL (PostgreSQL) — generating the boundary inline: `sql SELECT * FROM events WHERE created_at >= EXTRACT(EPOCH FROM NOW() - INTERVAL '30 days')::int AND created_at <= EXTRACT(EPOCH FROM NOW())::int; `

Common Mistakes to Avoid

Using local midnight instead of UTC midnight. If your application server is in a different timezone than UTC, new Date().setHours(0,0,0,0) in JavaScript gives you midnight in the server's local timezone — not UTC. Always be explicit about timezone.

Off-by-one on end boundaries. created_at <= 1711929599 (last second of March 31) and created_at < 1711929600 (midnight of April 1) are equivalent, but the second form is cleaner and avoids sub-second edge cases if you ever switch to milliseconds.

Forgetting DST transitions. When converting date ranges in timezones with daylight saving time, one day per year is 23 hours and one is 25 hours. This affects "last 24 hours" calculations: now - 86400 is not always "yesterday at this time" in local time. For precise local-day queries, generate the boundaries from the local calendar date explicitly rather than subtracting fixed second counts.

Not indexing the timestamp column. Obvious in principle, but frequently missed in early development when tables are small and slow queries aren't noticed yet.

Use the Unix timestamp converter to sanity-check your boundary timestamps before deploying a query — paste the integer in and confirm it converts to the date and time you expect.