How to Convert Time Zones in ParadeDB

Galaxy Glossary

How do I convert UTC timestamps to local time in ParadeDB?

AT TIME ZONE converts timestamp or timestamptz values between time zones in ParadeDB/PostgreSQL-compatible databases.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why convert time zones in ParadeDB?

Applications often store all timestamps in UTC but must display local times to users. ParadeDB inherits PostgreSQL’s rich time-zone functions, letting you shift timestamps on the fly without duplicating data.

What is the basic syntax?

Use the AT TIME ZONE construct or the timezone() function.Both accept any valid IANA time-zone string such as 'America/New_York' or offsets like '+02'.

How does AT TIME ZONE work in two directions?

• timestamp AT TIME ZONE zone → timestamptz
• timestamptz AT TIME ZONE zone → timestamp
The operator converts to or from UTC automatically based on the data type.

How to convert UTC order times to local time?

Retrieve Orders.order_date (stored as timestamptz in UTC) and show it in Pacific Time:

SELECT id, order_date AT TIME ZONE 'America/Los_Angeles' AS order_date_pt FROM Orders;

How to store user-supplied local time safely?

If a UI sends “2024-05-01 09:00” in the user’s zone, cast to timestamp and attach the zone:

INSERT INTO Orders (customer_id, order_date, total_amount) VALUES (42, ('2024-05-01 09:00'::timestamp AT TIME ZONE 'America/Los_Angeles'), 129.99);

Best practices for ParadeDB time-zone handling

Store all times as timestamptz in UTC.Convert only for display. Always use named zones, not numeric offsets, because offsets change with daylight-saving rules.

What are common mistakes?

See below for pitfalls and fixes.

Why How to Convert Time Zones in ParadeDB is important

How to Convert Time Zones in ParadeDB Example Usage


-- List New York local order times and total_amount for a customer
SELECT o.id,
       o.order_date AT TIME ZONE 'America/New_York' AS order_time_ny,
       o.total_amount
FROM   Orders o
WHERE  o.customer_id = 7
ORDER  BY order_time_ny DESC;

How to Convert Time Zones in ParadeDB Syntax


-- Convert a UTC timestamptz column to a specific zone
SELECT column_name AT TIME ZONE 'Zone' AS alias
FROM   table_name;

-- Attach a zone to a naive timestamp (makes timestamptz)
SELECT timestamp_column AT TIME ZONE 'Zone';

-- Function variant
SELECT timezone('Zone', timestamptz_value);

-- Example with ecommerce tables
-- Show Orders times in customer’s zone stored in Customers.timezone
SELECT o.id,
       timezone(c.timezone, o.order_date) AS local_time
FROM   Orders o
JOIN   Customers c ON c.id = o.customer_id;

Common Mistakes

Using numeric offsets like '+09' instead of named zones. Offsets ignore daylight-saving changes, producing wrong times. Fix by switching to IANA names, e.g., 'Asia/Tokyo'.
Forgetting to cast a text literal to timestamp before AT TIME ZONE. Without the cast ParadeDB errors. Fix by writing '2024-05-01 09:00'::timestamp AT TIME ZONE 'UTC'.