SQL：将查询中的时间戳从 GMT 偏移到太平洋
我希望有人可以帮助 SQL 新手（注意，我很新手，所以请温柔点）。我正在尝试帮助对我公司的一些承包商员工进行审计，看看他们是否可能在报告的时间表上榨取他们的时间。他们远程工作，所以我们看不到他们来访。去。我正在运行的是一个查询，以查看其条目中的第一个和最后一个时间戳，然后按天汇总它们，以便我可以了解他们在给定日期工作的小时数。因此，我正在运行的查询看起来像这样：
SELECT MIN(entry_time) AS first_time_stamp, MAX(entry_time) AS last_time_stamp, employee
FROM some_database.some_table
WHERE employee = 'Kevin' AND entry_time > '20110301'
GROUP BY DATE_FORMAT(entry_time, '%Y%m%d'), employee
;
这将返回一些类似于以下示例的数据。
'2011-03-01 01:10:58', '2011-03-01 17:53:43', 'Kevin'
'2011-03-02 12:00:47', '2011-03-02 20:36:59', 'Kevin'
'2011-03-03 01:34:58', '2011-03-03 21:37:22', 'Kevin'
到目前为止一切都很好，只是你会注意到“凯文”似乎工作了很长时间。问题是时间戳是 GMT，而我们位于美国西海岸。例如，显示的第一个时间戳实际上是 2 月 28 日，并且可能是他当天的最后一个条目。
在过去的一天里，我在谷歌上搜索了如何在选择这些时间戳之前将它们偏移-08:00，但总是出现空白或困惑（通常两者都是）。任何人都知道我如何能够偏移 entry_time 以便它根据偏移时间返回值？
请注意，我实际上并不需要担心标准时间与夏令时，因为我只是在寻找相对工作时间。
谢谢！
I'm hoping someone can help out a SQL newbie (note, I'm very green so please be gentle). I'm trying to help do an audit of some contractor employees at my company and see if they might be milking their hours on their reported time sheets. They work remotely so we don't see them come & go. What I'm running is a query to see the first and last timestamps in their entries and then roll them up by day so I can get an idea of the number of hours they're spending doing work on a given date. So the query I'm running looks something like this:
SELECT MIN(entry_time) AS first_time_stamp, MAX(entry_time) AS last_time_stamp, employee
FROM some_database.some_table
WHERE employee = 'Kevin' AND entry_time > '20110301'
GROUP BY DATE_FORMAT(entry_time, '%Y%m%d'), employee
;
This returns some data that looks like the following sample.
'2011-03-01 01:10:58', '2011-03-01 17:53:43', 'Kevin'
'2011-03-02 12:00:47', '2011-03-02 20:36:59', 'Kevin'
'2011-03-03 01:34:58', '2011-03-03 21:37:22', 'Kevin'
So far so good, except that you'll notice that "Kevin" seems to be working some really long days. The issue is that the timestamps are in GMT and we're on the west coast in the US. So for example, the first time stamp that shows up would've actually been on February 28th and might have been his last entry of that day.
I've spent the past day Googling for ways to offset these timestamps by -08:00 before they're selected but keep coming up empty or confused (usually both). Anyone out there know how I might be able to offset the entry_time so that it's returning values based on the offset time?
Note that I don't really need to worry about Standard vs Daylight Saving times since I'm just really looking for the relative hours worked.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
函数 http://dev .mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz 在时区之间转换时间戳，并且应该执行您需要的操作。 （我假设您正在使用 MySQL。）
The function http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz converts timestamps between timezones and should do what you need. (I assume that you're using MySQL.)