topic Re: Timestamp in UTC in Qlik Replicate

Timestamp in UTC

simonB2020 — Fri, 30 May 2025 13:42:52 GMT

Trying to use Global rules to add a UTC timestamp column to each table/record reflecting when processed by QR Task.
Just tried the below but on selecting results in Redshift it is showing "2025-05-30 17:27:25.000"
It is currently 13:27 UTC

```
datetime('now', 'utc')
```
Appreciate any guidance

Re: Timestamp in UTC

john_wang — Fri, 30 May 2025 13:51:48 GMT

Hello @simonB2020 ,

Please try to use:

datetime('now', '-4 hours')

Hope it works for you.

Regards,

John.

Re: Timestamp in UTC

simonB2020 — Fri, 30 May 2025 13:53:51 GMT

Thanks John,
Unfortunately that is going to fall over as soon as DST hits.

Re: Timestamp in UTC

john_wang — Fri, 30 May 2025 15:26:19 GMT

Hello @simonB2020 ,

You're absolutely right, and I appreciate your correction. I apologize for my earlier inaccurate explanation.

The expression datetime('now', '-4 hours') always subtracts 4 hours from UTC, regardless of whether the local time is currently observing standard time or daylight saving time. This makes it unsuitable for DST-aware calculations.

Additionally, both datetime('now') and datetime('now', 'utc') return the current UTC time. It's important to note that this expression is evaluated on the Qlik Replicate server, not the source or target databases.

To better understand the behavior, I’d like to clarify the following:

Is there a time zone difference between the Qlik Replicate server and the Amazon Redshift cluster?
This could affect the interpretation of datetime values if one is in UTC and the other in a local time zone.
Can you confirm the actual datetime values directly within Redshift using a native client or console (e.g., AWS Query Editor)?
Please be cautious, as some third-party tools (e.g., dbeaver) may automatically adjust and display timestamps according to the local time zone, which can lead to confusion.

Let me know what you find, and I’ll be happy to assist further based on the time zone settings in your environment.

Best Regards,

John.

Re: Timestamp in UTC

DesmondWOO — Tue, 03 Jun 2025 02:18:53 GMT

Hi @simonB2020 ,

Could you try using datetime('now')? Additionally, you can test the result in the Expression Builder.

Regards,
Desmond