Help in converting Epoch date to something readable

greddin
greddin used Ask the Experts™
on
I've table with a column called LastLoggedIn that's stored in numeric format. I have found this is a Java/Unix epoch format.

A sample is: 1264419761994

Could someone show me the syntax to query and convert this to a standard readable date/time stamp?

My table name is JiveUser and my field/column is called LastLoggedIn.

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Try this:
select TO_DATE('1970-01-01', 'YYYY-MM-DD') + LastLoggedIn / 86400000 from JiveUser
/
David VanZandtOracle Database Administrator III

Commented:
Hey, remember to search the E-E knowledge base and save your points, this has been asked numerous times since 2005.  One solution:  http://www.experts-exchange.com/Database/Oracle/Q_21390736.html
Most Valuable Expert 2011
Top Expert 2012

Commented:
assuming that count is milliseconds, try this...


 to_date('1970-01-01','yyyy-mm-dd') + 1264419761994/86400000
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Should add that you can then format it with anything you want using TO_CHAR:

select TO_CHAR(TO_DATE('1970-01-01', 'YYYY-MM-DD') + LastLoggedIn / 86400000,'MM/DD/YYYY HH24:MI:SS') from JiveUser
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> save your points,

there are no points to be saved, premium users have unlimited points

Author

Commented:
I'm trying these queries but the first just comes back with:

01-Jan-70

and the second one is returning only:

01/01/1970 00:00:00
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>and the second one is returning only:


Hardcoding the value works for me:

SQL> select to_char(to_date('1970-01-01','yyyy-mm-dd') + 1264419761994/86400000,
'MM/DD/YYYY HH24:MI:SS') from dual;

01/25/2010 11:42:42

SQL>
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> the first just comes back with:

>>> and the second one is returning only:


please post sample data and expected results,  its quite possible we're misinterpreting what your counter offset is supposed to represent

Author

Commented:
This query:
select to_char(to_date('1970-01-01','yyyy-mm-dd') + 1264419761994/86400000,'MM/DD/YYYY HH24:MI:SS') from dual;

Returns:
01/25/2010 11:42:42

Which looks good.

But when I change to my tablename and column:
select to_char(to_date('1970-01-01','yyyy-mm-dd') + LastLoggedIn/86400000,'MM/DD/YYYY HH24:MI:SS') from JiveUser;

It just returns the same thing over and over for each row:
01/01/1070 00:00:00
01/01/1070 00:00:00
01/01/1070 00:00:00
01/01/1070 00:00:00
01/01/1070 00:00:00
... etc
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Is it possible that some rows actually have 'epoch' for the values?

need some sample data:

select LastLoggedIn from JiveUser where rownum<11;

Author

Commented:
I just realized that a lot of the rows have values of "0" where the user has never logged in before.

So was just never scrolling down far enough to see the ones besides the 01/01/70 00:00:00 ones.

I modified the query like this:
select to_char(to_date('1970-01-01','yyyy-mm-dd') + LastLoggedIn/86400000,'MM/DD/YYYY HH24:MI:SS') from JiveUser where LastLoggedIn <> 0;

Which using your conversion works fine.

Sorry for the confusion.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Sorry for the confusion.

No problem...  happens to ALL of us...

Author

Commented:
Thanks for the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial