Storing datetime in database?

Question

I'm working on a blog and want to show my posts in eastern time zone. i figured that storing everything UTC would be the proper way. This creates a few challenges though:

1. I have to convert all times from UTC to Eastern. This is not a biggie but adds a lot of code.

2. And the "biggie" is that I use a short-date time to reference the posts by passing in a query, ala blogger. The problem is that there is no way to convert the short date time to the proper UTC date because I'm lacking the posted time info.

Hmm, any problem to just storing all dates in eastern time? This would certainly make it easier for the rest of the application but if I needed to change time zones everything would be stored wrong.

Update

@Jon, very much value your expertise, but I've decided storing UTC time in database is wrong. You may be able to convince me otherwise though!

So, let me give you the full skinny of it. I converted all the times to UTC, which required adding a lot more "syntax" in the declarative parts of the code.

What I'm coding is a blog, so I have 2 routes, a list route and a details route. The details route takes a short date and the name of the post which is assumes the combined to be unique. This is why it breaks down. I pass the short route in the query.

Depending on Time of Day, we can wrap around so that it is 29 or 30. So the short date must be correct because I don't have enough information in the query to determine how to convert the eastern time back to UTC.

I was storing a DatePosted "short date" with all times @ 12 AM for SQL purposes and a FirstModified long date for the time. So, I said, okay no problem, I'll just store the short date in eastern and then convert long date to eastern. No go because I can't make those query work.

So, I decided I went about this all wrong. The date should always be stored in eastern time (the time I want to use). And then if the user wants to change time zone (not a requirement), we just go through all the entries and change them.

Maybe, I'm thinking about this wrong but in my case there will only ever be eastern time zone used, so it is a waste of time either way.

Look forward to any input on this!

The key problems:

1. I'm passing in a short date in the query. If this short date is in UTC then the date in the query could be off by 1. If I pass the short date as eastern then I will need a linq-to-sql syntax that can converted to SQL that will allow me to compare date times of different time zones.

Thanks again for all help!

Answer 1

Converting times from UTC to Eastern time shouldn't add "a lot of code". It should be pretty much a one-liner.

I'm not sure what you really mean by your second point - it would help if you'd elaborate on it. If you don't know the time zone involved, how can you even understand the query? If you're effectively assuming it's in Eastern time, then you can do the appropriate conversion to UTC.

Using a local time throughout is generally a bad idea. If you've only ever got one time zone involved then it's not too bad, but if you ever want to use other time zones, it'll become horrible. (It won't just be a matter of bulk updating the database - it'll be a case of examining every single use of dates throughout the application.)

Go for the UTC option, IMO.

Answer 2

Store the times in UTC and convert them in your view/page/whatever to Eastern or, even better, to the local time of the client.

Also, as others said, converting UTC to Eastern time shouldn't require "a lot of code," as your basically subtracting some hours from the time while remaining slightly aware of daylight savings.

For instance, consider

DateTime currentServerTime = DateTime.UtcNow.ToLocalTime(); 

Answer 3

It's just a blog and not a business web app used by offices around the world, so I say go ahead and use Eastern Time.

If you ever changed time zones, and wanted to display the new time zone, one SQL update statement can update all the times to the new zone.

Answer 4

Store the values in the db in UTC format then convert to local time at the client. That way, each client sees times in their local frame of reference and you don't need to concern yourself with specific timezones.