0

I am trying to convert EPOCH to a HUMAN DATETIME.

  • When I test this on their website, it returns the right value.
  • When I do it within SQL Developer, it's wrong.

This lead me to check other basic SQL to see how they get returned, 

select sysdate from dual = 12-OCT-2015

It's missing the time? 

SELECT TO_CHAR(TO_DATE('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS'))
FROM dual; 

= 15-MAY-15

Again its missing the time?

SELECT TO_DATE('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS')
FROM dual; 

= 15-MAY-15

With out TO_CHAR, still missing time.

Then my EPOCH SQL, 

SELECT TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')+(1325289600000/60/60/24) AS EPOCH FROM dual;

This should return: Sat, 31 Dec 2011 00:00:00 GMT but it returns: 04-OCT-30

Again, TIME is missing.

SERVER is on EST, so time is out by 5 hour Oracle Server 11g Using SQL DEveloper 4.0.2.15

Thanks for any help, Ben

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Runawaygeek
  • 115
  • 3
  • 13
  • 1
    Possible duplicate of [How can I set a custom date time format in Oracle SQL Developer?](http://stackoverflow.com/questions/8134493/how-can-i-set-a-custom-date-time-format-in-oracle-sql-developer) – MT0 Oct 12 '15 at 11:29
  • It returns. But your local time format (for displaying) does not display it. – ibre5041 Oct 12 '15 at 11:30
  • Your *epoch* includes milliseconds, you need to divide it by 1000: `1325289600000/1000/60/60/24` – dnoeth Oct 12 '15 at 11:40

4 Answers4

2

Mask should be in TO_CHAR function as well to output time.

SELECT TO_CHAR(TO_DATE('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS'), 'YYYY/MM/DD HH:MI:SS')
FROM dual;

But it will be character string. If you want to do something with this date (add something and so on), you should put TO_CHAR last (first do everything with date, then put TO_CHAR with mask)

Tatiana
  • 1,489
  • 10
  • 19
  • I ran your script above, it says SQL command not properly ended ?? **Scratch that, my bad Cut and paste!, it worked fine** but you are right, its now a String. – Runawaygeek Oct 12 '15 at 11:31
  • Also, when added to my EPOCH query it returns zero, SELECT TO_CHAR(TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')+(1325289600000/60/60/24),'DD/MM/YYYY HH24:MI:SS') AS EPOCH FROM dual; – Runawaygeek Oct 12 '15 at 11:38
2

Its missing the time?

No, it is there, you are just not displaying it. Or your client's locale-specific NLS settings are not correctly set to display the time portion.

A date always has both date and time portions, which is internally stored by Oracle in a 7-byte proprietary format.

At individual SQL level, to display a date in your desired format, always use TO_DATE along with the proper FORMAT MODEL.

For example,

Session level

SQL> alter session set nls_date_format='YYYY-MM-DD';

Session altered.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
----------
2015-10-12

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
-------------------
2015-10-12 17:08:18

Individual SQL level Will override session and client's NLS settings:

SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;

TO_CHAR(SYSDATE,'YY
-------------------
2015/10/12 17:09:58
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1

It returns the correct value but SQL developer is not set up to display it correctly.

From this answer:

You can change this in preferences:

  1. From Oracle SQL Developer's menu go to: Tools > Preferences.
  2. From the Preferences dialog, select Database > NLS from the left panel.
  3. From the list of NLS parameters, enter DD-MON-RR HH24:MI:SS into the Date Format field.
  4. Save and close the dialog, done!

(I prefer the ISO standard format YYYY-MM-DD HH24:MI:SS but whichever you prefer will work.)

Then if you do:

SELECT TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')
         +(1325289600000/60/60/24) AS EPOCH
FROM dual;

The output for DD-MON-RR HH24:MI:SS format will be:

EPOCH            
------------------
04-OCT-30 00:00:00

and the output for YYYY-MM-DD HH24:MI:SS would be:

EPOCH             
-------------------
4030-10-04 00:00:00
Community
  • 1
  • 1
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thank you for solving issue 1, i can now see the time value. However, the value is wrong. 1325289600000 should be 31-DEC-2011 00:00:00?? http://www.epochconverter.com/ – Runawaygeek Oct 12 '15 at 11:41
  • `1325289600000/60/60/24` is `15339000` days which is `42024.65` years. Did you mean `SELECT TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS') +(1325289600000/1000/60/60/24) AS EPOCH FROM dual;` instead? – MT0 Oct 12 '15 at 11:46
  • Thank you, i was missing the milliseconds (/1000) to get the right level of conversion. Thank you very much! – Runawaygeek Oct 12 '15 at 11:46
0

Your epoch includes milliseconds, you need to divide it by 1000:

SELECT TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')
         +(1325289600000/1000/60/60/24) AS EPOCH
FROM dual;
dnoeth
  • 59,503
  • 4
  • 39
  • 56