Avoiding Date Night Disasters in Oracle APEX: Handling BST and Time Zones Properly

By BG -

So you’re going on a date. You tell your better half you’ll meet her at the fine dining restaurant at 8 PM.
She arrives at 7 PM, waits almost an hour, then leaves — heartbroken, annoyed, and perhaps plotting her revenge. 😬

What went wrong?

Simple: You were both in different time zones… metaphorically speaking.

But that’s exactly what happens in Oracle APEX when you mishandle dates, particularly when dealing with British Summer Time (BST) and time zone conversions in workflows.

The APEX Date-Time Trap

Let’s say you capture a date and time from a user using a date picker in an APEX form. You think you’ve got a precise timestamp to use in a workflow or scheduled activity.

But then something strange happens — your workflow fires an hour early. Why?

The culprit: Time zone confusion

Behind the scenes, Oracle APEX stores date and timestamp values using the database session time zone, which is often UTC. If the user selected “8:00 PM BST” on the form, and you didn't properly convert that to a time zone-aware timestamp, Oracle interprets it as “8:00 PM UTC”.

That’s 7:00 PM BST. Exactly one hour early. Just like the date night disaster.

Understanding the Key Oracle Functions

To fix this, you need to explicitly handle time zones using Oracle’s built-in date-time functions.

1. FROM_TZ(timestamp, timezone)

This function associates a time zone with a TIMESTAMP value that doesn’t already have one. This is crucial when you're converting a user-submitted date from an APEX form (which is likely in local time) into a proper time zone-aware timestamp.

FROM_TZ(TO_TIMESTAMP(:P1_DATE, 'YYYY-MM-DD HH24:MI'), 'Europe/London')

This says: "Take this timestamp value (which we assume was entered in BST/local time) and associate it with the Europe/London time zone."

2. AT TIME ZONE

Once you have a time zone-aware timestamp, you can convert it to another time zone (like UTC, which is often what your DB or workflow engine runs on).

FROM_TZ(TO_TIMESTAMP(:P1_DATE, 'YYYY-MM-DD HH24:MI'), 'Europe/London') 
AT TIME ZONE 'UTC'

Now you’ve taken the date from the form (entered in BST), interpreted it correctly in London time (which handles daylight savings automatically), and converted it to UTC for workflow processing.

Why This Matters in Workflows

Imagine you’re using Oracle APEX to drive a workflow engine or schedule future events (emails, escalations, task timers). If you skip proper time zone handling, these events may trigger too early or too late, depending on whether Daylight Saving Time is active.

The more global your application becomes, the more painful this gets.

BST (British Summer Time) and GMT (Greenwich Mean Time) are not the same! From late March to late October, the UK is one hour ahead of GMT. That’s why time zone-aware conversions are critical.

Best Practices for Working with Dates in APEX

  • Always assume APEX form inputs are in the user's local time.

  • Use FROM_TZ to associate the correct time zone to that value.

  • Use AT TIME ZONE to convert the value to the time zone expected by your system logic (often UTC).

  • Prefer using TIMESTAMP WITH TIME ZONE in PL/SQL or SQL queries for anything time-sensitive.

  • Document your assumptions — is the time local to the user? Server? UTC?

TL;DR

Your workflows are only as reliable as your date math. If you skip proper time zone handling, you're setting yourself (and your users) up for disappointment.

So next time you're building a time-sensitive feature in APEX, remember:
Don’t just say "8 PM" — say "8 PM in Europe/London".
Your database — and your significant other — will thank you.

Comments

Post a Comment

Popular posts from this blog

MSDTC in Windows Core

By BG -
Simple one this... How can we enable inbound transactions in MSDTC on a core machine? Well we use PowerShell of course! :-) PS C:\>Set-DtcNetworkSetting -InboundTransactionsEnabled $True Nice one.
Read more

The Dreaded 403 PowerShell Remoting Error

By BG -
Image
If you have worked with PowerShell remoting then you will have seen this error before: : Connecting to remote server BLAH failed with the following error message : The WinRM client received an HTTP status code of 403 from the remote WS-Management service. For more information... It is not a happy message, especially when you have been using PowerShell to remotely manage the particular server for ages! So then you try remoting from another client and it works! You go back to your original client and try remoting to anything else and it fails...dohh! "But this worked just yesturday!" you scream. Ahh, little things can make a big difference and in my case the issue was related to a VSTS Agent update that I did the day before. In order for the new version of the agent to communicate with VSTS in the cloud I needed to set a WinHTTP proxy. Once the agent was configured I could use a .proxy file in the agent directory instead...but I forgot to remove the WinHTTP proxy and in s...
Read more

Biztalk vs OIC

By BG -
Image
 So we are developing integration solutions using Biztalk 2016 and recently we started working with Oracle's Integration Cloud iPaaS service. We have found it quite painful and limiting and until this week have been singing the praises of Biztalk, saying that it's just far easier to work with and more functionally rich. Well... This week I had what seemed a simple task. Take an OAGIS BODFailureMessage (OAGIS is awesome) and send the details to Service Desk Plus, then put the resultant Request ID back into the original message and send it onwards. Seemed pretty straightforward. Problem #1 Service Desk Plus has an API that enables us to POST a Request BUT the API does not accept a message-body; it wants you to send the details of the request (which is a JSON payload) as a parameter called input_data.  Now from Postman, this is quite easy. Simply add a key-value pair in the Body using the x-www-form-urlencoded option like so: However, when it comes to doing the same thing in Bi...
Read more