Learn How To Optimize SuiteQL Queries: Handling Timezone Offsets in NetSuite

This article is relevant if you are using NetSuite’s SuiteQL (SQL) inquiries and dealing with datetime data.

Background

In computer science, dates can be a complex data type to handle.  A simple way to think about a date is to consider that a text representation, such as “February 28, 2025,” can be converted into a decimal number representing the offset number of days from January 1, 1970 (UTC).  Furthermore, since NetSuite runs on Oracle in data centers hosted around the planet, we must account for the nuances of that system.

Contextually, my 2015 article, still relevant in 2025, NetSuite Server Side TimeZone Settings, discusses the complexities of how time (hours and minutes) is displayed to the end user and how it is worked with via SuiteScript (1.0 for that era). The topic is nuanced and I recommend having a review.

When working with SuiteQL (SQL) in NetSuite at the programming level, it’s important to note that queries will not return results in the user’s timezone (via Home, Set Preferences, Localization) without some help. This is because we are working close to the API, not NetSuite’s user interface tier, which does some of that timezone translation work automatically. So, what should we do if we need to display date/time information that is contextually correct for the end user?

SuiteQL Tricks to Determine User’s Timezone Offsets

I was impressed by our Technology Practice analysts’ approach to expressing timezone information using SQL. Their method is insightful and provides a straightforward way to adjust timestamps for end users.

Step 1: Identify Hour/Minute User-Based Timezone Offsets

The first step is determining the difference between the user’s timezone and the database-level timezone. The following SQL query calculates the offsets:

SELECT 
    TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'TZH')) - TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZH')) AS hours,
    TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'TZM')) - TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZM')) AS minutes
FROM DUAL;

Step 2: Apply Offsets to Adjust Date/Time Information

With these offsets, we can now adjust timestamps to reflect the user’s timezone. The query below demonstrates this by comparing the native database timestamp with the adjusted timestamp.

As I write this and produce the screenshots, my user-preference is in the US Mountain Timezone.   Our company-wide preference is the Pacific Timezone.  NetSuite’s system clock, as discussed in NetSuite Server Side TimeZone Settings, operates in Pacific Time — this is likely a legacy decision from the 1990s that didn’t account for global expansion (how did they miss that, God only knows). Ideally, the system clock should have been set to UTC.

Here’s how we apply the timezone offset dynamically:

SELECT TOP 5
    id,
    name,
    lastmodifieddate || ' ' || TO_CHAR(lastmodifieddate, 'HH24:MI:SS') AS lastmodified_native_tz,
    lastmodifieddate + (tzoffset.hours / 24) + (tzoffset.minutes / 1440) 
        || ' ' || TO_CHAR(lastmodifieddate + (tzoffset.hours / 24) + (tzoffset.minutes / 1440), 'HH24:MI:SS') AS lastmodified_user_tz,
    BUILTIN.DF(folder) AS folder_text,
    BUILTIN.DF(filetype) AS filetype_text
FROM file
JOIN (
    SELECT 
        TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'TZH')) - TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZH')) AS hours,
        TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'TZM')) - TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZM')) AS minutes
    FROM DUAL
) tzoffset ON 1 = 1
WHERE isinactive = 'F'
AND folder = -4;

This approach ensures that timestamps are adjusted dynamically based on the user’s actual timezone offset, making date/time data contextually relevant.

Click the images to see the query results in action.

Join a Team of Strong NetSuite Leaders

This article is for analysts looking to deepen their SQL expertise within the NetSuite platform.  SuiteQL has become central to the applications and utilities we produce for our clients.  Hence, mastering its nuances is key.

Readers may notice that the SuiteQL tool showcased here isn’t the typical one referenced in the marketplace.  That’s because we built it using React while enhancing our Extensible Client Framework — a high-performance NetSuite app development framework designed to push past perceived NetSuite performance and usability limits.

Further Reading for Technology-Minded Readers

Explore more SuiteQL insights:

1. Render NetSuite SQL Queries Like Saved Searches
2. Learn How To Update NetSuite using SQL
3. Content Renderer Engine 2.0 with NetSuite SuiteQL

All these tools (bundled as Accelerator Templates) are available to our clients license-free as foundational components for business-driven solutions.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you would like to join the best-in-class NetSuite Systems Integrator, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube