Introduction
I would like to verify how UTC time and local time are handled in SharperLight.
To do this, I prepare a test datamodel, database, and data using SharperLight Foundry.
Preparing the resources
The table where test data is stored is defined like this.
Then I define these fields in the table.
CreatedDateTimeUTC and CreatedDateTime are the fields required this time.
CreatedDateTimeUTC field stores the UTC datetime, and CreatedDateTime field stores the local datetime.
After clicking the Execute button, Sharperlight Foundry creates all the resources for me, including sample data.
Verification
Database
The first of all, I check the database created.
I can see this sample data is in the table of the database.
The sample data was populated at 2024-12-12 10:14:25.000 local time in Perth Australia (+8:00).
I can see the CreatedDateTimeUTC holds UTC datetime because it is 8:00 hours behind.
Datamodel
Next, I have a look the datamodel definition.
Open the generated datamodel with Sharperlight Studio, and go for those two datetime fields definition to check their SQL Data Type attribute.
They are defined fine.
SQL Data Type tells Sharperlight Engine "Please deal with this value in this data type".
Sharperlight Query
Let's see Sharperlight query can deal with those fields well.
I can see a Published Report in Publisher generated by the Foundry definition, so use it.
Open it and check the query, and the query definition is like this.
Click preview it and check how those two datetime fields are dealt with.
As you can see, CreatedDatetTimeUTC field shows local datetime rather than its UTC datetime.
Why?
We checked the actual data is stored in UTC in the database table, and the datamodel definition is also fine. Why the value is shown in local datetime?
There is one more option we need to check.
Go back to the query definition, open the output option for the column CreatedDatetTimeUTC.
Date Options says Default, and it means the datetime value will be converted to local datetime when it is shown.
Therefore, Date Time UTC must be set when it needs to be shown in UTC datetime.
After updating it, it shows UTC datetime like this.
Output Options must say Date Time UTC when the value needs to be shown in UTC.
Afterword
In this post, I just say "Local datetime", I am in Perth Australia so the timezone is UTC+8:00.
If you are in Japan, it must be UTC+9:00.
I hope if this post help your work.