Get Date from a long var char milliseconds value stored in oracle Database
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
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
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,
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?
Have a look here for 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.
Also, it looks like your value is 13 digits long rather than 10, so you maybe just need to add some additional zeroes, ie divide by 86400000 rather than 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.
I'm a little unclear about how Oracle handles time zones but after a bit of experimenting I came up with the following examples
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.
@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:
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.
B. Converting a string to a number in Oracle is fairly easy. I'd just use the TO_NUMBER function, as in:
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:
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.
We wanted to convert long values stored in the database to TIMESTAMP values in the central timezone.
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.
链接地址: http://www.djcxy.com/p/36938.html