How to use a TIME literal in a SOQL Query

soqltime

I am building a dynamic SOQL query that may have a TIME field in the where clause:

select Id,Name,Customer__c,Load__c,Actual_Arrival_Date_D__c,Actual_Arrival_Time_D__c,Actual_Departure_Date_L__c,Actual_Departure_Time_L__c,Available_Date_D__c,Available_Time_D__c 
 from Container__c 
 where Actual_Arrival_Date_D__c >= 2022-03-02 and
       Actual_Arrival_Date_D__c <= 2022-03-04 and 
       Actual_Arrival_Time_D__c = 01:00:00.000 
 order by Name limit 1000

In this case Actual_Arrival_Time_D__c is a Time field.

The query pukes with this error:

line 1:329 no viable alternative at character ' ': System.QueryException: Class.ContainerConsoleController.applyContainerFilter: line 154, column 1

How to I format/escape the time literal on the right side of the clause? Thanks!

Best Answer

I haven't been able to find any relevant documentation for this, so it's time to get empirical.

I created a Test_Time__c field (field type Time) on Account in my dev edition org, found myself a record in the web UI, and populated this field with a time.

Using a plain 'ol query in the developer console (SELECT Id, Name, Test_Time__c FROM Account), I got the following

Id Name Test_Time__c
0011N0000xxxxxx test Account 01:00:00.000Z

That gives us a decent idea of what the format should be. It looks like just the time part of a datetime literal, so let's give that a try.

[SELECT Id, Name, Test_Time__c FROM Account WHERE Test_Time__c = 01:00:00.000Z]

Running this query in the dev console query tab works. Copying the body of the query into a string and executing it using Database.query() also works.

So...

  • You need to use a leading zero for numbers less than 10
  • The milliseconds are optional, and can be given as any arbitrary precision (e.g. 01:00:00.00000000000000000000000000000Z is fine), though Salesforce will only actually store up to 3 decimal places
  • You need to use a timezone indicator at the end of the literal (Z, or +/- 00:00 style, e.g. 01:00:00-06:00)
  • Do not enclose the time literal in quotes
  • Time is mod 24, so 01:00:00Z == 19:00:00-06:00 == 04:00:00+03:00
  • Entered through the web UI, the user's timezone is completely ignored (so even in UTC-6 land, entering the time as 1 am meant it got stored as 01:00:00Z)
Related Topic