Skip to main content

We, as shipping company, have users which have to log their vessel activities. Our vessels cross the sea and travel all over the world, meaning they travel through different timezones. All our datetime fields are being logged in UTC time, however it would be easier for our users to log in Local Time based on the selected port.

 

Lets say, the Captain of the Vessel wants to log their Departure in port “Taicang”. Now the Captain needs to calculate the time in UTC before being able to log their activity. It would be much easier for them to log in Local Time, so that we calculate the UTC time for them. We also have “Planners” who would like to communicate with Local Time towards the Captains.

 

In short, we would like to make use of Local Time in a lot of places within our application. While doing research I found the following methods to calculate from/and to Local Time.

  • Use of a query like follows:

    • SELECT @UtcTime AT TIME ZONE 'China Standard Time' AS TaicangTime

      • Downside: In some ports, the timezone changes at some point to daylight time. I am not completely sure if we can manage this correctly. 

  • Use of sys.time_zone_info
    • Downside: Timezone data is apparently retrieved from files on the computer. Which I am not sure if it is reliable.

 

What do you suggest? Or is there already functionality made by Thinkwise to retrieve Local Time? 

Hi @Marijn Metzlar,

I don't consider myself an expert on this subject, but what I can do is refer you to some  documentation we already provide on this topic:

Perhaps someone else can elaborate a bit more on your questions, but I hope this will already clarify some things for you.

Hi @Jeroen van den Belt ,

Thanks for your reply 😀. I want to emphasize that we want to retrieve the local time of the selected port. This is not always the same timezone as where the user is located.

A user here in the Netherlands possibly wants to log a datetime in China (based on the local time of China).

Hi @Marijn Metzlar , 
We made a solution to store the timezone in a country table.
When you know then the country of the port or city or so, you can create logic to recalculate the time from local to utc before storing it in the database.

Other option is to select also the timezone together with the time and then recalculate the time to utc.

Maybe this will help you.

Hi @HJ van Dalfsen , 

We were also thinking in the same direction. How do you tackle daylight saving time and countries with more than 1 timezone?

 

Hi @Marijn Metzlar 

We created a function to convert timezones from 1 to another using the code below:
 

return @datetime AT TIME ZONE @from_time_zone AT TIME ZONE @to_time_zone

Then daylight saving time is no issue.
See also https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver16

Multiple timezones per country was for our solution not needed, otherwise you need to register the timezone per region or so with multiple regions per country.

Reply


Terms & ConditionsCookie settings