|
|
|
Re: converting Unix Time format to readable format [message #682230 is a reply to message #682229] |
Sun, 11 October 2020 14:50 |
|
talhaparvaiz@yahoo.com
Messages: 14 Registered: October 2020
|
Junior Member |
|
|
Hi,
I ran 2 codes... first one was to extract the dates (unix format) and second one is to convert dates from Unix to readable format . My first SQL ran fine and second gave the formatting error. Please advise
-------------SQL 1---------------
select
json_value(json_value, '$.baseline_end') astarget_end_date,
json_value(json_value, '$.baseline_start') astarget_start_date
fromjir.entity_property
whereentity_ID = 705435and
Entity_name = 'IssueProperty'
-----------Output-------------
TARGET_END_DATE TARGET_START_DATE
1601424000000 1530403200000
-------------SQL 2---------------
select
to_char(to_date('01-JAN-1970 00:00:00', 'DD-MON-RRRR HH24:MI:SS -5:00') + (json_value(json_value, '$.baseline_end'))/(3600*24) - 5/24, 'DD-MON-RRRR HH24:MI:SS') astarget_end_date,
json_value(json_value, '$.baseline_start') astarget_start_date
fromjir.entity_property
whereentity_ID = 705435and
Entity_name = 'IssueProperty'
-----------Output-------------
errormessage
ORA-01821: dateformat notrecognized
|
|
|
Re: converting Unix Time format to readable format [message #682232 is a reply to message #682230] |
Sun, 11 October 2020 14:56 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Sun, 11 October 2020 10:22
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The test case must be representative of your data and different cases you have to handle.
...
[Updated on: Sun, 11 October 2020 14:57] Report message to a moderator
|
|
|
Re: converting Unix Time format to readable format [message #682233 is a reply to message #682230] |
Sun, 11 October 2020 15:28 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Do you realize DATE format has no time zone? Also, is it UNIX seconds, milliseconds, microseconds time format? I'll assume microseconds:
with data as (
select '{"team_ID":"123","parent_id": "1234","baseline_end":"16014240000000","baseline_start":"76014230000000"}' val from dual
)
select timestamp '1970-01-01 00:00:00 -5:00' + numtodsinterval(json_value(val,'$.baseline_start') / 1000000,'second') start_ts,
timestamp '1970-01-01 00:00:00 -5:00' + numtodsinterval(json_value(val,'$.baseline_end') / 1000000,'second') end_ts
from data
/
START_TS END_TS
------------------------------------ ------------------------------------
1972-05-29 19:03:50.000000000 -05:00 1970-07-05 08:24:00.000000000 -05:00
SQL>
SY.
|
|
|
|
|
|
Re: converting Unix Time format to readable format [message #682241 is a reply to message #682235] |
Mon, 12 October 2020 06:09 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
talhaparvaiz@yahoo.com wrote on Sun, 11 October 2020 18:37got it
I changed the / 1000000 to /1000 and it worked
Really?
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'
2 /
Session altered.
SQL> SELECT DATE '2020-09-29' - 16014240000000 / 1000 / 60 / 60 / 24 DT
2 FROM DUAL
3 /
DT
-------------------
1513/04/01 00:00:00
SQL>
It appears you need to divide by 10000 but then you'll get 2020/09/30, not 2020/09/29:
SQL> SELECT DATE '1970-01-01' + 16014240000000 / 10000 / 60 / 60 / 24 DT
2 FROM DUAL
3 /
DT
-------------------
2020/09/30 00:00:00
SQL>
SY.
|
|
|