【General】UTC datetime and Local datetime

# 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5597d100-e86e-7d79-efe3-350ab21bd863.png)

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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4fc389d7-764b-a18c-177b-57e317ee50b4.png)

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.

::: note warn
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. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8264f7db-c1b7-ccd9-fe7a-c7770ecf4824.png)

### 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ffddd403-1837-94a0-130a-7ae148dfc042.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bd66e7b8-f4ec-7adc-9c61-705991b72457.png)

:::note warn
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9027a583-ddd9-fb4f-9a0c-7fab6c27aef3.png)
Open it and check the query, and the query definition is like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eb35b8ac-0bb5-0a08-11f9-30202f080d07.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8d17ffd6-2561-248e-0388-13d060dfc064.png)

**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**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b109a516-62f7-8967-4198-ca3ef45f3606.png)

`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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c3c600b4-d4da-83a2-03d6-38eeedc3b174.png)

::: note warn
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.