将DateTime从UTC时区的用户位置Convert DateTime from UTC to user location timezone

- 此内容更新于:2015-01-06
主题:

在db我拯救DateTime UTC是这样的: 并为每个用户我省时区在这种格式“+ 0200”,“-0300”,“-0430” 现在我想按日期过滤结果 在SQL:SELECT *从表名CreatedDate =获取当前日期() 如何增加价值从时区选择dateTime列?

原文:

In db I save DateTime in UTC like this:

CreatedDate = DateTime.UtcNow;

and also for every user I save timezone in this format "+0200", "-0300", "-0430"

now I want to filter results by date

SQL: SELECT * FROM tableName WHERE CreatedDate >= GETDATE()

how add value from timezone column to the selected dateTime?

SteenT的回复:如果日期在数据库中存储为UTC日期,您应该使用GETUTCDATE()而不是获取当前日期()。

(原文:If dates in the database are stored as UTC dates, you should use GETUTCDATE() instead of GETDATE().)

解决方案:
你可以做类似的事情 然后将checkDate作为参数传递给查询
原文:

you can do something similar to this

   DateTime utcDateTime = DateTime.UtcNow;
   TimeSpan offSet = TimeSpan.Parse("+01:00:00");
   DateTime checkDate = utcDateTime + offSet;

and then pass checkDate as parameter to the query

Alex的回复:从服务器获取当前日期()将返回日期,但我需要使用时区值列

(原文:GETDATE() will be return date from the server, but I need to use value from column timezone)

faby的回复:是“+ 0200”存储在数据库中的一列吗?

(原文:is "+0200" stored in a column in the database?)

Alex的回复:

(原文:yes, is varchar(5))

faby的回复:我# 39;已经编辑我的答案,让我知道

(原文:I've edited my answer, let me know)

解决方案:
根据您所使用的语言,您可以从客户端应用程序,例如javascript。 让客户# 39;s时区JavaScript 这样你不需要存储在DB。 添加时间回到你的例子,例子返回使用解析和http://msdn.microsoft.com/en-gb/library/ms186819.aspx
原文:

Depending on the language you are using, you can get this from the client application, e.g. for javascript.

var offset = new Date().getTimezoneOffset();

Getting the client's timezone in JavaScript

This way you don't need to store it in the DB.

To add the time back in for your example, use parsing and DATEADD http://msdn.microsoft.com/en-gb/library/ms186819.aspx

解决方案:
是这样的: 未经考验的! 编辑 或使用这样的:(未测试) .ToArray TimeZoneInfo[]tz = TimeZoneInfo.GetSystemTimeZones()(); t1 = tz字符串。在第一个(t = t。BaseUtcOffset = = TimeSpan.FromHours(Math.Round((double.Parse(存储)/ 100),1))).Id; 时间= TimeZoneInfo DateTime。ConvertTimeBySystemTimeZoneId(storedDate,t1);
原文:

Something like this:

storedDate.AddHours(double.Parse(timezoneOffset)/100).ToString();

Untested!

EDIT

Or use something like this: (Again, untested)

TimeZoneInfo[] tz = TimeZoneInfo.GetSystemTimeZones().ToArray();
string t1 = tz.First(t => t.BaseUtcOffset == TimeSpan.FromHours(Math.Round((double.Parse(stored) / 100), 1))).Id;
DateTime time= TimeZoneInfo.ConvertTimeBySystemTimeZoneId(storedDate, t1);
Joanvo的回复:不要# 39;t那样做!,赢得了# 39;t考虑特殊情况,比如日光节约时间。

(原文:Don't do that! That won't take into account special cases, such as daylight saving times.)

ispiro的回复:@Joanvo看看这个问题。它看起来像OP有足够的信息来检查?不。他没有# 39;t保持这些信息,因此这# 39;年代是不可能的。(不是所有的地方都在一个时区都有相同的日期永久夏时制时间,还是改变时钟。)

(原文:@Joanvo Look at the question. Does it look like the OP has enough information to check that? No. He didn't keep that information, and therefore that's impossible anyway. (Not all places in one time zone have the same dates for daylight saving times, or do they change the clocks at all.))

ispiro的回复:@Joanvo无论如何,编辑。

(原文:@Joanvo Anyway, edited.)