How to Convert Dune Timestamps to Local Time: Block Time and Time Zone Handling

Block times in Dune are stored in UTC (Coordinated Universal Time) by default, and the query results you see are also in UTC. To convert them to your local time, simply specify your time zone (e.g., 'Asia/Shanghai') using the AT TIME ZONE or CONVERT_TZ function in your query.

1. Understand the Default Format of Time Fields in Dune

Dune's block time fields (such as block_time, block_timestamp) are stored as timestamp with time zone type, with UTC as the default time zone.

The values you see directly in query results are UTC. If your local time zone is not UTC (for example, Beijing time is UTC+8), the raw time will appear 8 hours behind.

Prerequisite: You have already written a SQL query in Dune's query editor and can see returned time fields like block_time.

2. Practical Steps: Convert to Your Local Time in a Query

Step 1: Identify your target time zone

• What to do: Specify your time zone using your city or a UTC offset.

• How to do it: The standard format is 'Region/City', e.g., 'Asia/Shanghai' (Beijing time), 'America/New_York' (Eastern Time). You can also use an offset like 'UTC+08:00', but the city-based format automatically handles daylight saving time.

• Completion criteria: You know exactly which time zone you want to convert to.

Step 2: Use AT TIME ZONE in your query

• What to do: In your SELECT statement, apply the AT TIME ZONE operator to convert UTC time to the target time zone.

• How to do it: SELECT block_time AT TIME ZONE 'Asia/Shanghai' AS local_time FROM ...

• Completion criteria: The local_time column returned by the query shows a time that is 8 hours ahead of the raw block_time (for Beijing time).

Common mistake: Filtering directly on the converted time inside a WHERE clause may cause index invalidation or slow down the query due to time zone conversion. It is recommended to keep the original UTC time range in the WHERE clause and only apply the conversion in the SELECT output.

Risk warning: Time auxiliary tables in Dune such as utils.hours and utils.days also generate time series in UTC. If you join these tables (e.g., LEFT JOIN utils.hours), remember to convert both block_time and utils.hours.timestamp to the same time zone before joining; otherwise, they won't align.

How to verify the conversion

On the converted time column, extract the hour using EXTRACT(HOUR FROM ...) and compare it with your current local hour. If they match, the time zone conversion has taken effect. For dashboard display, you can embed the conversion logic directly in the chart query, so viewers see the time in their local zone.