---
title: Use date and time in SQL queries
slug: n23f0266
canonical_url: https://docs.coveo.com/en/n23f0266/
collection: coveo-analytics
source_format: adoc
---
# Use date and time in SQL queries
When you run SQL queries in Snowflake to retrieve your data, you must ensure that the timezone is adjusted as needed.
This is essential as it ensures that the date and time on your data accurately matches the user's browser timezone.

By default, date and time are always set to [UTC](https://en.wikipedia.org/wiki/Coordinated_Universal_Time) in our [**Raw Data**](https://platform.cloud.coveo.com/admin/#/orgid/usage/raw-data/) ([platform-ca](https://platform-ca.cloud.coveo.com/admin/#/orgid/usage/raw-data/) | [platform-eu](https://platform-eu.cloud.coveo.com/admin/#/orgid/usage/raw-data/) | [platform-au](https://platform-au.cloud.coveo.com/admin/#/orgid/usage/raw-data/)) sources, hence the need for time conversion.
There are different ways to adjust time zones.
In this article, we provide basic guidelines and examples based on our own methods when retrieving data.

> **Note**
>
> In the [Coveo Administration Console](https://docs.coveo.com/en/183/), the UTC timezone is converted to the user's browser timezone with the [`CONVERT_TIMEZONE()` function](https://docs.snowflake.com/en/sql-reference/functions/convert_timezone).
> 
> After the conversion, the Snowflake `DATE_TRUNC()`	 function is used to truncate the timestamp to the required interval (for example, day, month, etc.).
> For example:
> 
> ```sql
SELECT
	DATE_TRUNC(
		'day',
		CONVERT_TIMEZONE(
			'UTC',
			'America/New_York',
			'2019-01-02 00:00:00' :: timestamp_ntz
		)
	) as time_conversion;
```
> 
> results in:
> 
> ![Snowflake SQL results showing truncated date in query result table](:https://docs.coveo.com/en/assets/images/coveo-analytics/snowflake-date-trunc-result.png)
> 
> Notice the difference between the date in the query and the resulting date.
> In the query:
> 
> * the `CONVERT_TIMEZONE()` function converted `UTC` to the `America/New_York` timezone, therefore subtracting 5 hours from the time.
> 
> * the `DATE_TRUNC()` function truncated the date by day, which brought the resulting date to the beginning of the date (`2019-01-01 00:00:00`).

## Set timezone

As we filter data based on timestamps, we want the time constant to be in the target timezone and to match the timezone we convert the data to.
Before running any query, it's essential that you set your session to the target timezone.
For example:

```sql
ALTER SESSION SET timezone = 'America/New_York';
```

The timestamp constant in your query will then match the timezone to which you're converting the data in Snowflake.
For example:

```sql
SELECT '2022-11-30 23:59:59.999'::TIMESTAMP_TZ;
```

results in:

![Snowflake SQL results showing timezone conversion example](https://docs.coveo.com/en/assets/images/coveo-analytics/snowflake-timezone-result.png)

## Filter by date and time

When you filter data by time periods, the filter starts from the first millisecond of the start date to the last one of the end date.
For example, if you adjust the period from January 6, 2023 to February 5, 2023, the filter would look like the following: `2023-01-06 00:00:00.000` to `2022-02-05 23:59:59.999`.

When you want to want to apply a timestamp filter, the SQL expression would look like the following example:

```sql
CONVERT_TIMEZONE('UTC', 'America/New_York', "datetime") BETWEEN '2022-11-01 00:00:00.000' :: TIMESTAMP_TZ
AND '2022-11-30 23:59:59.999' :: TIMESTAMP_TZ
```

## Calculate visit duration

In the Coveo Administration Console, the [**Visit Browser**](https://platform.cloud.coveo.com/admin/#/orgid/usage/visit/) ([platform-ca](https://platform-ca.cloud.coveo.com/admin/#/orgid/usage/visit/) | [platform-eu](https://platform-eu.cloud.coveo.com/admin/#/orgid/usage/visit/) | [platform-au](https://platform-au.cloud.coveo.com/admin/#/orgid/usage/visit/)) provides the visits duration, which is computed from the browser itself.

You can compute the visits duration in a SQL query by adding the `TIMEDIFF()` function.
For example:

```sql
SELECT
	"visit_id" AS visit_id,
	MAX(
		CONVERT_TIMEZONE('UTC', 'America/New_York', "datetime")
	) AS start_time,
	MIN(
		CONVERT_TIMEZONE('UTC', 'America/New_York', "datetime")
	) AS end_time,
	TIMEDIFF('second', min("datetime"), max("datetime")) AS duration_seconds
FROM
	"coveo"."ua"."all_events_shared"
WHERE
	CONVERT_TIMEZONE('UTC', 'America/New_York', "datetime") BETWEEN '2023-01-10 00:00:00.000' :: TIMESTAMP_TZ
	AND '2023-01-12 23:59:59.999' :: TIMESTAMP_TZ
GROUP BY
	"visit_id"
LIMIT
	100;
```