How to fetch Date/Time from [Time Source] in different time zone other than UTC?

MVP Expert
MVP Expert

The following filter queries in the authorization source "[Time Source]" is fetching date time from insighdb in UTC. How to fetch Date/Time in different time zones other than UTC?


Sample Output:

insightdb=> select date_trunc('hour', localtimestamp(0)) as today,
date_trunc('hour', localtimestamp(0)+ interval '1 days') as one_day,
date_trunc('hour', localtimestamp(0)+interval '1 week') as one_week,
date_trunc('hour', localtimestamp(0)+ interval '1 month') as one_month,
date_trunc('hour', localtimestamp(0)+ interval '6 months') as six_months;
        today        |       one_day       |      one_week       |      one_month      |     six_months
 2016-12-20 19:00:00 | 2016-12-21 19:00:00 | 2016-12-27 19:00:00 | 2017-01-20 19:00:00 | 2017-06-20 19:00:00


The below output will confirm the default time of ClearPass database is in UTC.

insightdb=> select now();
 2016-12-20 19:51:01.14256+00




From the ClearPass version 6.6.x and higher, the database time is set to UTC by default despite of whatever the time zone the server is in. The data will be stored in UTC and fetched in the same format, so It will not affect any functionalities in the server.

However, when you try to validate date/time from an external server against local db date/time, you may need conversion from UTC to local time.

For example: Consider the "Endpoint:Last Check In" time fetched from MDM server is in IST and you need convert the local DB time to IST to validate the "last check in" time should be within last 24 hours.


Last Check In attribute sample from Configuration >> Identity >> Endpoints.


The below filter query will help you to convert the local time to IST.

# select localtimestamp at time zone '+05:30' ;

 2016-12-21 01:42:29.834674+00


The query can further be modified to achieve the above requirement.

# select date_trunc('hour', (localtimestamp(0) at time zone '+05:30') - interval '1 days') AS last_24hours;

 2016-12-20 01:00:00+00

Another example:

# select date_trunc('hour', (localtimestamp(0) at time zone '+05:30') + interval '12 hours') AS now_plus_12hours;

2016-12-21 13:00:00+00


The filter query can be added in the [TIme Source] as shown below.







Below is an example condition to use the filter query result in an Enforcement Policy  to check whether the endpoint last check in time is within last 24 hours and allow access.




Version history
Revision #:
2 of 2
Last update:
‎01-10-2017 08:23 AM
Updated by:
Labels (1)
Search Airheads
Showing results for 
Search instead for 
Did you mean: