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 开发者_JAVA技巧 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, i.e. 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.