Best Practice using Timestamps / Timezones

BIZ TMS Aurelius
1

Our Client-App using Aurelius and RemoteDB has to handle different Timezones.

So for example 2 clients in different timezones are saving their Data within same database.
What are the best practices dealing this requirement with Aurelius and RemoteDB?

1 Like
2

First thing I'd suggest is that you save your dates consistently in your database, i.e,, make sure all the dates are saved in UTC timezone, or a specific timezone you use the most.

3

Our Solution for PostgreSQL looks like this:

RemoteDB:

  • TRemoteDbDatabase.OnRequestSending
    Setting Header containing client-timezone
  • TRemoteDBModule.CreateConnection override
    Getting Header and giving it to the FireDAC-Connection
  • Setting PGAdvanced Parm in FireDAC-Connection from recieved Header

Aurelius:

  • registered Own PostgreSQLGenerator (inherited from TPostgreSQLGenerator)
    DefineColumnType override
    case FieldType of ftDateTime, ftTimeStamp, Column.DataType = 'TIMESTAMP WITH TIME ZONE'

I think this is the easiest Way without changing TMS-Code :wink:

Are there any suggestions, points, why we should not do this in that way?

1 Like
4

That's a valid approach, if it's working for you, it's fine. There are many options, it really depends on your setup and actually how much of your code you want to modify.

In Aurelius-only mode, you could create virtual properties and work as local date-time, but internally convert the values to/from UTC times, which are effectively saved in the database.

With RemoteDB it's more complex indeed, if you want it to work transparently from your code at client side. I don't have the knowledge myself about how FireDAC works with PostgreSQL time zones, so I can't comment much on your specific solution. But I'm glad it's working. :-)

5

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.