How to display PostgreSQL database Datetime in specific timezones in Bold Reports

In Bold Reports, when using the PostgreSQL database, datetime values may be in a different timezone than the desired display timezone. Bold Reports does not handle datetime or timezone-related aspects on its own; Instead, it directly fetches the table data from the PostgreSQL provider and displays it in the preview area. This purely depends on the application’s timezone.

To ensure that datetime values are displayed in a specific timezone in Bold Reports, you can address it at the query level. For example, if your database stores datetime values in UTC timezone and a user fetches that data in the ‘US/Eastern’ timezone, it may display the wrong datetime values in the report rendering process, as it uses the user’s machine timezone. To resolve this issue, you can address it at the query level. If you are in the ‘US/Eastern’ timezone, you can set the same timezone in your query. This ensures that the correct time is consistently retrieved in both PostgreSQL and Bold Reports.

Here is an example query that demonstrates how to convert the current timestamp to the ‘US/Eastern’ timezone:

SELECT
    (current_timestamp AT time zone 'US/Eastern')::timestamp with time zone AS withTimeZone,
    (current_timestamp AT time zone 'US/Eastern')::timestamp AS withoutTimeZone;

In this query, the withTimeZone column contains the current timestamp in the ‘US/Eastern’ timezone, while the withoutTimeZone column contains the current timestamp in UTC.

Use Cases:

1. This solution can be used for any user who wants to display datetime in a different timezone in Bold Reports.
2. It is especially useful for users located in different timezones than the application’s default timezone.

When encountering timezone differences between PostgreSQL and Bold Reports, it is recommended to address the issue at the query level. By using the AT TIME ZONE function in the SQL query, datetime values can be converted to the desired timezone. This ensures consistent and accurate datetime display in Bold Reports. The provided example query demonstrates how to convert datetime values to a specific timezone, but users can modify it according to their requirements. By following this generic solution, users can overcome timezone-related challenges when displaying dates in Bold Reports.