Grow’s databases are set to show times in UTC (Coordinated Universal Time). If you don’t regularly go by UTC you may run into issues trying to use SQL to to set a specific time period. While the data manipulation toolbar in Edit Metric is in your browser’s timezone, if you use any SQL in your metric, the time will show in UTC.
To offset the timezone, you'll need to write a little bit of SQL.
For our example, the user wants to set the time frame to 30 days back.
When you go to your Edit Metric page, check the Advanced Options box. In the SQLite transformer box, enter the following:
WHERE date >= date('now','x hours','-30 days') AND
date <= date('now','x hours')
In this case, x is the number of hours you are offset from Coordinated Universal Time (UTC). (UTC is essentially the same as GMT.) The strings
date >= date('now','x hours','-30 days') and the string
date <= date('now',’x hours') sets the time frame to show from 30 days back until now. You can change this time frame depending on your metric. If you don’t know how to, feel free to chat in on the site and we can help you write it.
If you are having trouble figuring out how far offset you are from UTC, you can always Google search "your location vs. UTC."
In the example below, this metric is 4 hours behind UTC, so for the hours, the user would write
The syntax for this SQL text is as follows:
SELECT *means select all columns
FROMa specific report
WHEREindicates the column name, and then we use the date operator to offset them timezone.
Depending on what time frame you want to set for your metric, take a shot at writing the SQL (if you know how) or reach out to your data rep to set the correct time frame.
Here are examples of some common timezones for our customers:
- Mountain Standard Time (MST):
- UK (London):
As always, if you need help, you can reach out to us at firstname.lastname@example.org or chat in on the site and we’ll be happy to help.