Getting local time of different time zones

Use case

You need to get  time based on the time zones.

Solution

1. Switch the page to the edit mode.

2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.

3. Select the macro and click Edit.

4. In the Presets tab select Custom transformation and click Next.

5. Enter the following SQL query:

   SELECT *,
   FORMATDATE(DATEADD(hour, T1.'GMT' + (T1.'Created'::Date->getTimezoneOffset() / 60), T1.'Created'))
   AS 'My Time',
   FORMATDATE(DATEADD(hour, T1.'GMT' + ("today"::Date->getTimezoneOffset() / 60), NOW()))
   AS 'Current Time'
   FROM T1

   Copy

   SQL

6. Click Next.

7. Define the date format .

8. Save the macro and the page.

The getTimezoneOffset() method returns the time difference between UTC time and local time, in minutes.

The NOW() function returns the current date and time.

The  DATEADD() function adds a time/date interval to a date and then returns the date.

The FORMATDATE() function converts time/dates to the specified in the Table Transformer macro settings date format.