Home » SQL & PL/SQL » SQL & PL/SQL » converting Unix Time format to readable format
converting Unix Time format to readable format [message #682227] Sun, 11 October 2020 09:22 Go to next message
talhaparvaiz@yahoo.com
Messages: 10
Registered: October 2020
Junior Member
one of my oracle column has a date in Unix Time format.

can you please advise how to convert that into a readable format

thanks
Re: converting Unix Time format to readable format [message #682228 is a reply to message #682227] Sun, 11 October 2020 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
Previously ASKED & ANSWERED here

http://www.orafaq.com/forum/s/?SQ=e1de8b117beb91e9310c89e911bea968&t=search&srch=%22unix+time%22&btn_submit=Search&fi eld=subject&forum_limiter=&attach=0&search_logic=AND&sort_order=DESC&author=
Re: converting Unix Time format to readable format [message #682229 is a reply to message #682227] Sun, 11 October 2020 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And maybe you should thing about:
1/ Read the forum rules we have pointed you to and informed
2/ Comply to them
3/ Feedback in your topics
4/ Thank people who spend time to help you or just try to help you.

Re: converting Unix Time format to readable format [message #682230 is a reply to message #682229] Sun, 11 October 2020 14:50 Go to previous messageGo to next message
talhaparvaiz@yahoo.com
Messages: 10
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3007
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 #682234 is a reply to message #682233] Sun, 11 October 2020 17:27 Go to previous messageGo to next message
talhaparvaiz@yahoo.com
Messages: 10
Registered: October 2020
Junior Member
Hi,

The output that I was expected (based on Unix date to date conversion through https://freeformatter.com/epoch-timestamp-to-date-converter.html) is as follows

Unix format date: 16014240000000
should be: 9/29/2020
SQL is converting to: 05/29/1972

Please assist

Thanks
Re: converting Unix Time format to readable format [message #682235 is a reply to message #682234] Sun, 11 October 2020 17:37 Go to previous messageGo to next message
talhaparvaiz@yahoo.com
Messages: 10
Registered: October 2020
Junior Member
got it

I changed the / 1000000 to /1000 and it worked

last question.

how can I change the date format to
MM/DD/YYYY HR:MM:SS AM/PM
Re: converting Unix Time format to readable format [message #682239 is a reply to message #682235] Mon, 12 October 2020 00:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle version?

Have a look at TO_CHAR function in your version documentation.

Re: converting Unix Time format to readable format [message #682241 is a reply to message #682235] Mon, 12 October 2020 06:09 Go to previous message
Solomon Yakobson
Messages: 3007
Registered: January 2010
Location: Connecticut, USA
Senior Member
talhaparvaiz@yahoo.com wrote on Sun, 11 October 2020 18:37
got 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.
Previous Topic: Matrix Report Out put
Next Topic: ORA-06502
Goto Forum:
  


Current Time: Sun Nov 29 02:24:26 CST 2020