0

I'm programming a web application that clients can make appointments and choose a time: e.g: the admin has set the appointment times from 8 AM till 8 PM based on his own timezone (users timezoneId are saved in the database ) admin timezone: +3:30 There is +05:30 time difference between this client and admin . a client visits the site and tries to make an appointment for tomorrow from 10 AM to 10:30 AM. the client wants 10 AM based on his/her own timezone if we compute it based on the admin timezone 10 AM would be 3:30PM till 4:00 PM

  1. what datatype I have to use for saving time in DB (SQL server) DateTime or datetimeoffset?
  2. is there any implementation service for converting from another time zone to another? ( i have used timezoneinfo and DateTime convert built-in methods but all of them had problems)
  3. is daylight saving an important thing? when I use convert methods (TimeZoneInfo.ConvertTimeFromUtc())it considers daylight saving but when I use this method(TimeZoneInfo.ConvertTimeToUtc()) I think it doesn't consider daylight saving. I don't know that is my system design is correct or not isn't it better to save all times in the 00:00 timezone even with admin configs? I'm so confused can someone help me? I have tried DateTime and datetimeoffset built-in methods and also timezoneinfo built-in methods but I couldn't handle it
Vivek Nuna
  • 25,472
  • 25
  • 109
  • 197
Araz Mrt
  • 1
  • 3
  • Please edit your question to include basic formatting, such as line breaks. It's really quite hard to read as it is – canton7 Jan 24 '23 at 12:23
  • @canton7 I agree with you, for now I have done it for OP – Vivek Nuna Jan 24 '23 at 12:38
  • Hi. Please read [*How do I ask a good question?*](https://stackoverflow.com/help/how-to-ask). Specifically, you have too many questions rolled into one here. You should ask discrete questions. Also, it's hard to tell what you mean by "all of them had problems", because you haven't shown any example code or described those problems. – Matt Johnson-Pint Jan 24 '23 at 20:12
  • On the three items: 1) That depends on lots of things, but probably `datetime2` (a .NET `DateTime`) storing the local time of the event, and *also* the time zone id of the event. 2) `TimeZoneInfo` is the main built-in facility and works as designed. 3) Yes, DST is important, and yes, `ConvertTimeToUtc` does consider DST, but that depends on the time zone and on the `Kind` property of the `DateTime` you're using. – Matt Johnson-Pint Jan 24 '23 at 20:21
  • You might also want to read some of these answers: [1](https://stackoverflow.com/a/66925598/634824) [2](https://stackoverflow.com/a/19170823/634824) [3](https://stackoverflow.com/a/19627330/634824) (and others). – Matt Johnson-Pint Jan 24 '23 at 20:26

2 Answers2

0

datetimeoffset includes both date and time including the offset from the UTC. so you can store the specific time zone time.

NodaTime by sir Jon Skeet is a very good library to solve timezone-related problems, I have personally used it. you can also refer TimeZoneConverter library.

I personally prefer storing the UTC DateTime in the database and shows the time specific to the user's time zone in such scenarios.

Defnility Daylight Saving Time is a very important factor to be considered when working across different timezone.

you have to do testing around different timezones, DST cases. I have personally used the library Bogus for generating different times during my testing.

Vivek Nuna
  • 25,472
  • 25
  • 109
  • 197
  • UTC is great for timestamping, but usually not appropriate for future scheduling of events. `datetimeoffset` isn't the best choice either, as the offset might change if the TZ/DST rules change in the future before the event comes to pass. Also, SQL Server indexes all `datetimeoffset` fields as their UTC equivalent, which can make querying cumbersome if you need to query by local time. For event time of future events, I would retain the original information that is supplied by the user, which is likely best stored in a `datetime2` field (for a single non-recurring event). – Matt Johnson-Pint Jan 24 '23 at 20:29
0

Finally i solved my problem .
now i use datetime instead of datetimeoffset.
I use DateTime.UtcNow() method to save current time in save changes.
I save user TimezoneId in database
i wrote 2 extension methods for datetime converters :
`

public static DateTime ToUserTime(this DateTime targetTime, string timeZoneId)
{
    var timezone = TimeZoneInfo.FindSystemTimeZoneById(timeZoneId);
    return TimeZoneInfo.ConvertTimeFromUtc(targetTime, timezone);
}
public static DateTime ToUtc(this DateTime targetTime, string timeZoneId)
{
    var timezone = TimeZoneInfo.FindSystemTimeZoneById(timeZoneId);
    return TimeZoneInfo.ConvertTimeToUtc(targetTime, timezone);
}

`

Thanks to everyone who helped with this question

Araz Mrt
  • 1
  • 3