Oracle SQL查询需要根据时区进行更改 [英] Oracle sql query needs to change based on timezone
问题描述
我有一个SQL查询来获取日期范围内的记录.当我分析数据时,我的查询工作发现了byut,我发现记录是根据GMT时区值重新记录的,从而使结果不正确.
I have a sql query to obtainrecords for a date range. My query works find byut when I analyzed data, I found that the records are retirved base don GMT timezone value, thus making the restults incorrect.
我从数据库中的unix epoch值中获取时间.
I get my time from a unix epoch value in database.
SELECT tableA.columnA,tableB.columnB
FROM tableA INNER JOIN tableB ON tableA.aId = tableB.aId
WHERE (to_date('1970-01-01 00:00:00 +10:00','yyyy-MM-dd hh24:mi:ss') + (tableB.epochValue/60/60/24/1000)) > to_date('--FROM_DATE--', 'yyyy-MM-dd hh24:mi:ss')
AND (to_date('1970-01-01 00:00:00 +10:00','yyyy-MM-dd hh24:mi:ss') + (tableB.epochValue/60/60/24/1000)) <= to_date('--TO_DATE--', 'yyyy-MM-dd hh24:mi:ss');
我想获取基于我的时区(GMT + 10)的记录,但是此脚本基于GMT时区获取数据. 我想知道如何通过时区来获取正确的日期对象
I want to obtain the records based on my timezone which is GMT+10 but this script gets data based on GMT timezone. I was wondering how to pass my timezone to obtain the correct date object
使用 http://www.epochconverter.com/,我获得了这些值 我的时代价值-1345079730 GMT:2012年8月16日,星期四GMT 您的时区:2012年8月16日星期四,格林尼治标准时间+10
using http://www.epochconverter.com/, I obtained these values my epoch value - 1345079730 GMT: Thu, 16 Aug 2012 01:15:30 GMT Your time zone: Thu Aug 16 2012 11:15:30 GMT+10
我的数据库是-Oracle Database 11g企业版11.2.0.2.0版-64位
My database is - Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
推荐答案
找到了我的问题的答案.
Found the answer to my question.
显然,在计算历元值时,您还需要考虑时区设置
apparently, you need to consider the time zone settings when you calculate epoch value as well
- 将纪元值增加10小时(10 * 60 * 60 * 1000毫秒)-纪元当前值位于格林尼治标准时间(GMT),因此将其设为EST(GMT + 10),
-
使用TO_TIMESTAMP_TZ而不是to_date
- add 10 hours (10*60*60*1000 milliseconds) to epoch value - epoch current value is in GMT so to make it EST (GMT+10), I added this.
Used TO_TIMESTAMP_TZ instead of to_date
SELECT tableA.columnA,tableB.columnB
FROM tableA INNER JOIN tableB ON tableA.aId = tableB.aId
WHERE (TO_TIMESTAMP_TZ('1970-01-01 00:00:00 +10:00','yyyy-MM-dd hh24:mi:ss TZH:TZM') + ((tableB.epochValue+(10*60*60*1000))/60/60/24/1000)) > to_date('##FROM_DATE## +10:00', 'yyyy-MM-dd hh24:mi:ss TZH:TZM')
AND (TO_TIMESTAMP_TZ('1970-01-01 00:00:00 +10:00','yyyy-MM-dd hh24:mi:ss TZH:TZM') + ((tableB.epochValue+(10*60*60*1000))/60/60/24/1000)) <= to_date('##TO_DATE## +10:00', 'yyyy-MM-dd hh24:mi:ss TZH:TZM');
这篇关于Oracle SQL查询需要根据时区进行更改的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!