KNOWLEDGE BASE

NOW() and TODAY() Functions Return Datetimes in Unexpected Time Zone


Published: 04 Mar 2013
Last Modified Date: 23 Oct 2023

Issue

Using the NOW() or TODAY() function in a calculated field, Tableau Desktop returns time in a different time zone, or when using a relative date filter the anchor date will be in a different time zone.

Environment

  • Tableau Desktop
  • PostgreSQL
  • Tableau Server Repository (PostgreSQL database)
  • Amazon Redshift
  • Live connection to data source hosted on machine in different time zone

Resolution

Option 1:

Use an extract. For more information, see Extract Your Data.


Option 2:

Use a calculation with the DATEADD() function to account for the time zone offset.

Example 1:

  • To convert UTC to PST (Pacific Standard Time), the formula: NOW()
  • Could become: DATEADD('hour', -8, NOW())

Note: During daylight savings time the time difference between UTC and PDT will be -7 hours.

Example 2:

To convert UTC to PST (Pacific Standard Time) for a relative date filter:

  1. Remove the date filter from the Filters shelf
  2. Select Analysis > Create Calculated Field
  3. In the Calculated Field dialog box that opens, do the following, and then click OK:
    • Name the calculated field. In this example, the calculated field is named "Last 3 Days Filter"
    • In the formula field, create a calculation similar to the following:

      [Date Field] >= DATEADD('day', -3, DATEADD( 'hour', -8, TODAY() ) )

  4. Drag [Last 3 Days Filter] to the Filter shelf.
  5. In the Filter dialog, check True and click OK.

 

Cause

When connecting live, Tableau Desktop queries the datasource for NOW() or TODAY(). If the data source is hosted on a machine in a different time zone, then the data source may return the time in that time zone.

Live connections to Redshift and PostgreSQL are designed to return UTC time due to inconsistencies in how these datasources handle datetime and time zone offsets.

 

Additional Information

To voice your support for the inclusion of this enhancement in a future product release, add your vote to the following Community Idea: Timezone conversion support.

Did this article resolve the issue?