Search results

How to convert a timestamp column from one timezone to another in the PostgreSQL data source

Bold BI application allows you to convert the timestamp column in the PostgreSQL data source from one time zone to another.

Steps to convert timestamp column to another time zone

  1. Refer to the Connecting Bold BI to PostgreSQL data source documentation to successfully connect to it.

  2. Drag and drop the table from the table schema in the data design view page.

    Drag table

  3. Click on the Expression icon and choose the timestamp column which needs to be converted to the local time zone as shown in the following image.

    Expression icon

    Datetime column

  4. Then add the following code to the timestamp column which needs to be converted from Greenwich Mean Time (GMT) to your local time zone in the Expression editor as shown in the following image and click Save.

    AT TIME ZONE 'GMT' AT TIME ZONE '+05:30'

    The above sample registers the timestamp column in the GMT format and converts the timestamp into Indian Standard Time(IST).

    Create Expression

    NOTE: The time difference from GMT to your local time zone should be set manually in the above sample.

  5. Click on the Update and you can find the result where the created timestamp column in GMT will be converted into Indian Standard Time.

    Result preview

    Note: This documentation works for all the web data sources in the extract mode only and PostgreSQL in live and extract modes in the Bold BI Cloud. For MS SQL Server and MySQL data sources, use the following expressions for converting the GMT date column to the Indian Standard Time zone.

    Data sources Expressions
    MS SQL Server DATEADD(mi,DATEDIFF(mi, GETUTCDATE(), GETDATE()),[last_requested])
    MySQL CONVERT_TZ([last_requested],'+00:00','+05:30')