Use date and time in SQL queries

This is for:

Business Analyst System Administrator

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 in our Raw Data (platform-ca | platform-eu | platform-au) 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, the UTC timezone is converted to the user’s browser timezone with the CONVERT_TIMEZONE() function.

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:

SELECT
	DATE_TRUNC(
		'day',
		CONVERT_TIMEZONE(
			'UTC',
			'America/New_York',
			'2019-01-02 00:00:00' :: timestamp_ntz
		)
	) as time_conversion;

results in:

Snowflake date trunc result

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:

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:

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

results in:

Snowflake timezone result

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:

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 (platform-ca | platform-eu | platform-au) 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:

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;