Hey there! Let's tackle your problem of querying data between 7:00 and 12:00 when your timestamp is stored as epoch time (those long 13-digit numbers in your example are millisecond-precision epoch timestamps, by the way). I'll walk you through a specific solution for your case first, then break down the general method you can reuse for any time window.

Specific Query for 7:00–12:00 Data #

You have two solid approaches here—pick whichever fits your workflow better:

Approach 1: Convert Epoch to Readable Time & Filter by Hour #

This is the more intuitive method if you don't want to calculate epoch values manually. The key is to convert your epoch timestamp to a human-readable datetime, then extract the hour to filter.

Since your timestamps are in milliseconds (13 digits), we'll need to divide by 1000 first for most databases (they expect second-precision epoch for conversion functions). Here are examples for common databases:

MySQL/MariaDB

SELECT ID, Timestamp
FROM your_table_name
-- Convert epoch ms to datetime, then check if hour is between 7 and 11 (covers 7:00:00 to 11:59:59)
-- Use >=7 AND <=12 if you want to include 12:00:00 exactly
WHERE HOUR(FROM_UNIXTIME(Timestamp / 1000)) BETWEEN 7 AND 11;

PostgreSQL

SELECT ID, Timestamp
FROM your_table_name
WHERE EXTRACT(HOUR FROM TO_TIMESTAMP(Timestamp / 1000)) BETWEEN 7 AND 11;

SQL Server

SELECT ID, Timestamp
FROM your_table_name
WHERE DATEPART(HOUR, DATEADD(ms, Timestamp, '1970-01-01 00:00:00')) BETWEEN 7 AND 11;

Approach 2: Calculate Epoch Range & Directly Compare #

This method is faster for large datasets because it avoids converting every row's timestamp (it can leverage indexes on the Timestamp column if you have one).

First, calculate the epoch milliseconds for the start and end of your window:

• 7:00 AM on your target date → convert this to epoch ms (e.g., for 2023-11-13, that's 1699927200000)
• 12:00 PM (noon) → use 1699945199999 to include up to 11:59:59.999, or 1699945200000 if you want to include 12:00:00 exactly.

Then run this query:

SELECT ID, Timestamp
FROM your_table_name
WHERE Timestamp BETWEEN 1699927200000 AND 1699945199999;

To calculate these epoch values quickly, you can use command-line tools (e.g., date -d "2023-11-13 07:00:00" +%s on Linux gives you second-precision epoch—multiply by 1000 for ms) or a simple script in Python/JavaScript.

General Method for Any Time Window with Epoch Timestamps #

Follow these steps whenever you need to filter by a specific time range:

1. Confirm epoch precision: Check if your timestamps are in seconds (10 digits) or milliseconds (13 digits). This determines whether you need to divide by 1000 when converting to datetime.
2. Choose your approach:
   • For readability: Convert epoch to datetime, then extract the time component (hour, minute, etc.) using your database's built-in functions (like HOUR(), EXTRACT(), or DATEPART()).
   • For performance: Calculate the epoch start and end values of your target window, then use BETWEEN or >=/<= to filter directly on the timestamp column.
3. Watch out for timezones: Epoch timestamps are UTC-based by default. If your business uses a local timezone, make sure to adjust the conversion (e.g., use CONVERT_TZ() in MySQL or AT TIME ZONE in PostgreSQL) to avoid off-by-hour errors.

内容的提问来源于stack exchange，提问作者Gokkul