从存储在 oracle 数据库中的 long var char 毫秒值中获取日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4312514/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Date from a long var char milliseconds value stored in oracle Database
提问by Harshana
I have a date store in data base as a long value in varchar eg: 1230748200000. I need to get the year out from it withing the oracle query..how can i please do it in oracle? is there a way that i can covert that in to a Date so that i can get the year? The problem is i cant use to_char(SYSTIMESTAMP,'yyyy') or to_Date because i dont have the date in Timestamp or Date format...instead having it as long varchar
我在数据库中有一个日期存储作为 varchar 中的一个长值，例如：1230748200000。我需要使用 oracle 查询从中获取年份..我怎样才能在 oracle 中做到这一点？有没有办法可以将其转换为日期，以便获得年份？问题是我不能使用 to_char(SYSTIMESTAMP,'yyyy') 或 to_Date 因为我没有时间戳或日期格式的日期......而是让它像 varchar 一样长
Thank You.
谢谢你。
Edit: Can you please give a solution for the following scenario. I have 1262284398000 in my table save as var char.so as i said i need to get year i use the following sql which return year as 2009
编辑：您能否为以下场景提供解决方案。我的表中有 1262284398000 保存为 var char.so 因为我说我需要得到年份我使用以下 sql 返回年份为 2009
select
to_char(
to_date('01-JAN-1970','DD-MM-YYYY HH24:MI SS') + ( 1262284398000 / (1000*60 * 60 * 24) ) ,'YYYY')
from dual ;
But in java code same value returns date as 2010 so the year is get diferent,
但是在 java 代码中，相同的值返回的日期与 2010 年相同，因此年份不同，
Calendar c = Calendar.getInstance();
c.setTimeInMillis(1262284398000l);
System.out.println(c.getTime());
System.out.println(c.get(Calendar.YEAR));
Which date return as Fri Jan 01 00:03:18 IST2010 Why is the difference here please?
哪个日期返回为 Fri Jan 01 00:03:18 IST2010 请问这里有什么不同？
回答by Mike Meyers
Have a look herefor further information
看看这里了解更多信息
select epoch_time, extract(year from epoch_time) as year
from (
select date '1970-01-01' + 1291116794/86400 as epoch_time
from dual
)
There may be issues around time zones etc since I believe the epoch time will be in UTC but that is beyond my ken.
时区等方面可能存在问题，因为我相信纪元时间将采用 UTC 时间，但这超出了我的理解。
Also, it looks like your value is 13 digits long rather than 10, so you maybe just need to add some additional zeroes, i.e. divide by 86400000 rather than 86400.
此外，看起来您的值是 13 位而不是 10，所以您可能只需要添加一些额外的零，即除以 86400000 而不是 86400。
EDIT:As I said above.... there may be time zone issues. It looks like the Java code is taking 1970-01-01 (in UTC, as it should be) adding the offset and converting it to local time. You will need to do that with the database query as well.
编辑：正如我上面所说的......可能存在时区问题。看起来 Java 代码需要 1970-01-01（在 UTC 中，应该是）添加偏移量并将其转换为本地时间。您还需要对数据库查询执行此操作。
I'm a little unclear about how Oracle handles time zones but after a bit of experimenting I came up with the following examples
我有点不清楚 Oracle 如何处理时区，但经过一些试验后，我想出了以下示例
alter session set time_zone = 'Asia/Calcutta';
select
sessiontimezone,
to_char(to_date('01-JAN-1970','DD-MM-YYYY HH24:MI SS') +
( 1262284398000 / (1000*60 * 60 * 24) ) ,'YYYY'),
extract(year from cast(timestamp '1970-01-01 00:00:00 +00:00' at local +
numtodsinterval(1262284398000 / 1000, ' SECOND') as timestamp with time zone)),
to_char(timestamp '1970-01-01 00:00:00 +00:00' at local +
numtodsinterval(1262284398000 / 1000, ' SECOND'), 'YYYY'),
extract(year from cast(timestamp '1970-01-01 00:00:00 +00:00' at local +
numtodsinterval(1262284398000 / 1000, ' SECOND') as timestamp with local time
zone))
from dual;
In the example above, the first two columns (after sessiontimezone) should be 2009 and the last two should be 2010. Although this has all just been found through experimentation so your mileage may vary.
在上面的例子中，前两列（在 sessiontimezone 之后）应该是 2009，最后两列应该是 2010。虽然这都是通过实验发现的，所以你的里程可能会有所不同。
回答by Bob Jarvis - Reinstate Monica
@MikeyByCrikey posted a good starting point, but there are a couple questions you need to answer before you can convert your string value to a date:
@MikeyByCrikey 发布了一个很好的起点，但是在将字符串值转换为日期之前，您需要回答几个问题：
A. What date is represented by '0'? That is, where does this millisecond count value start from? In @Mikey's example the Unix 'beginning' date (also called the 'epoch date') of 01-Jan-1970 is used, but there isn't enough information in your post to know what the '0' date actually is. Other systems use different epoch dates. For example, on systems running the VMS operating system the epoch date is 17-Nov-1858 (happens to be Julian day 2,400,000); Windows (32 and 64 bit versions) uses 01-Jan-1601 (first year of the 400 year Gregorian calendar cycle which was current when this OS was designed); and other computer systems use different '0' dates.
A. '0' 代表什么日期？也就是说，这个毫秒计数值是从哪里开始的？在@Mikey 的示例中，使用了 1970 年 1 月 1 日的 Unix“开始”日期（也称为“纪元日期”），但您的帖子中没有足够的信息来了解“0”日期实际上是什么。其他系统使用不同的纪元日期。例如，在运行 VMS 操作系统的系统上，纪元日期是 17-Nov-1858（恰好是儒略日 2,400,000）；Windows（32 位和 64 位版本）使用 1601 年 1 月 1 日（设计此操作系统时当前的 400 年公历周期的第一年）；和其他计算机系统使用不同的“0”日期。
B. Converting a string to a number in Oracle is fairly easy. I'd just use the TO_NUMBER function, as in:
B. 在 Oracle 中将字符串转换为数字相当容易。我只使用 TO_NUMBER 函数，如下所示：
strMillisecs VARCHAR2(100) := '1230748200000';
nMilliseconds NUMBER;
nMilliseconds := TO_NUMBER(strMillisecs);
C. Once you've gotten to this point (the '0' date is known, and the string is converted to a number) the year can be extracted as follows:
C. 一旦你到了这一点（'0' 日期是已知的，并且字符串被转换为一个数字），年份可以被提取如下：
dtEpoch_date DATE := TO_DATE('01-JAN-1970', 'DD-MON-YYYY'); -- for example
strMillisecs VARCHAR2(100) := '1230748200000';
nMilliseconds NUMBER;
dtTarget_date DATE;
nYear NUMBER;
nMillisecs_in_a_day NUMBER := 86400000;
nMilliseconds := TO_NUMBER(strMillisecs);
-- Note: there are
dtTarget_date := dtEpoch_date + (nMilliseconds / nMillisecs_in_a_day);
-- FINALLY!
nYear := TO_NUMBER(TO_CHAR(dtTarget_date, 'YYYY'));
Hopefully this is useful.
希望这是有用的。
Share and enjoy.
分享和享受。
回答by Kyle Dyer
We wanted to convert long values stored in the database to TIMESTAMP values in the central timezone.
我们想将存储在数据库中的长值转换为中央时区的 TIMESTAMP 值。
This worked.
这奏效了。
select FROM_TZ(
TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') +
NUMTODSINTERVAL( 1386655200000/1000,'SECOND'),
'UTC') at time zone 'America/Chicago'
from dual
Where 1386655200000 is the millisecond value of a java.util.Date on Dec. 10, 2013 in central timezone.
其中 1386655200000 是中央时区 2013 年 12 月 10 日 java.util.Date 的毫秒值。
