Program guide > Access data with client applications > Retrive entities and objects (Query API)
Query data in multiple time zones
In a distributed scenario, queries actually run on servers. When querying data with predicates of type calendar, java.util.Date and timestamp, the specified date time value in a query is based on the local time zone of the server.
In a single time-zone system where all clients and servers run on same time zone, you do not need to consider issues related to predicate types with calendar, java.util.Date and timestamp. However, when clients and servers are in different time zones, the specified date time value in queries is based on the server time zone and may return unwanted data back to client. Without knowing the server time zone, the specified date time value is meaningless. So the specified date time value should consider the time zone offset difference between the target time zone and the server time zone.
Time zone offset
For example, assume that a client is in [GMT-0] time zone and the server is in [GMT-6] time zone. The server time zone is 6 hours behind the client. The client would like to run the following query:
SELECT e FROM Employee e WHERE e.birthDate='1999-12-31 06:00:00'
Assuming the entity Employee has a birthDate attribute that is of type java.util.Date, the client is in [GMT-0] time zone and wants to retrieve Employees with birthDate value as '1999-12-31 06:00:00 [GMT-0]' based on its time zone.
The query will run on the server and the birthDate value used by the query engine will be '1999-12-31 06:00:00 [GMT-6]' that equals to '1999-12-31 12:00:00 [GMT-0]'. Employees with birthDate value equal to '1999-12-31 12:00:00 [GMT-0]' will be returned to the client. Thus, the client will not get wanted Employees with birthDate value '1999-12-31 06:00:00 [GMT-0]'.
The problem described occurs because of the time zone difference between client and server.
To solve this problem, one approach is to calculate the time zone offset between client and server and apply the time zone offset on the target date time value in the query. In the previous query example, the time zone offset is -6 hours, and the adjusted birthDate predicate should be “birthDate='1999-12-31 00:00:00'” if the client intends to retrieve Employees with birthDate value '12-31 06:00:00 [GMT-0]'. With the adjusted birthDate value, the server will use '1999-12-31 00:00:00 [GMT-6]' that equals to target value '12-31 06:00:00 [GMT-0]', and the required Employees will be returned to the client.
Distributed deployment in multiple time zones
If the distributed eXtreme Scale grid is deployed into multiple ObjectGrid servers in various time zones, the adjusting time zone offset approach will not work because the client will not know which server will run the query and thus cannot determine the time zone offset to use. The only solution is to use suffix ‘Z' (not case sensitive) on JDBC date and time escape format to indicate using GMT time zone based date time value. The suffix ‘Z' (not case sensitive) indicates to use GMT time zone based date time value. Without the suffix ‘Z', the local time zone based date time value will be used in the process that runs the query.
The following query is equivalent to the previous example, but uses the suffix ‘Z' instead:
SELECT e FROM Employee e WHERE e.birthDate='1999-12-31 06:00:00Z'
The query should find Employees with birthDate value ‘1999-12-31 06:00:00'. The suffix ‘Z' indicates the specified birthDate value is GMT time zone based, so the GMT time zone based birthDate value ‘1999-12-31 06:00:00 [GMT-0]' will be used by the query engine for matching criteria value. Employees with birthDate attribute value equal to this GMT based birthDate value ‘1999-12-31 06:00:00 [GMT-0]' will be included in query result. Using the suffix ‘Z' on JDBC date time escape format in any query is crucial to make applications time zone safe. Without this approach, the date time value is server time zone based and is meaningless from the client perspective when clients and servers are in different time zones.
For more information, see Insert data for different time zones.
Parent topic:
Retrive entities and objects (Query API)
Related concepts
Insert data for different time zones
Reference for eXtreme Scale queries
Use objects other than keys to find partitions (PartitionableKey interface)