Return to Jive Software

Up to Discussions in Support

This Question is Answered (go to answer)

2 "helpful" answers available (2 pts)
458 Views 4 Replies Last post: Nov 2, 2009 9:37 AM by William England RSS
William England Novice
William England
99 posts since
Nov 18, 2008
Currently Being Moderated

Oct 18, 2009 10:40 PM

LASTLOGGEDIN to Epoch (or excel) date

Hi!

 

CS 2.5.5 here.

 

In the jivecs.jiveuser table we have the LASTLOGGEDIN value in, as I found on this forum, a modified verison of UNIX epoch date.

 

I can't seem to reliably reverse engineer this string.

 

What's the pattern to rip this back to epoch date, so Excel can properly format it?

 

Why?  We want to do some reporting -- I know using code would be more approrpiate but due to various limitations, we can't do that.  But I do have a read only view in the database.

 

Thanks,

 

Will

Tags: date, epoch
Kevin Imber Jive Employee
Kevin Imber
293 posts since
Oct 19, 2009
Currently Being Moderated
Oct 30, 2009 2:02 PM in response to: William England
Re: LASTLOGGEDIN to Epoch (or excel) date

Hey William,

 

Looks to me to just be a regular 'time since epoch' in milliseconds. Running the linux command 'date +%s.%N' and then multiplying by 1000 should give you the current time in milliseconds.  What do you mean by 'ripping it back to epoch date, so excel can properly format it'?

 

Thanks,

Kevin

William England Novice
William England
99 posts since
Nov 18, 2008
Currently Being Moderated
Oct 30, 2009 6:55 PM in response to: Kevin Imber
Re: LASTLOGGEDIN to Epoch (or excel) date

The question was specifcally how to get it into Epoch Seconds for use in Excel.

 

Since I said Excel, assume I'm a business weenie and don't know Linux from a hole in my head.

 

But, I found it - y'alls search is getting spooky good.

http://www.jivesoftware.com/jivespace/message/211037#211037

 

 

Jive lastloggedin value is stored in miliseconds since the epoch - not seconds as Excel expects.

 

(lastloggedin / 1000) == epoch and can be manipulated by Excel.

Kevin Imber Jive Employee
Kevin Imber
293 posts since
Oct 19, 2009
Currently Being Moderated
Nov 2, 2009 8:55 AM in response to: William England
Re: LASTLOGGEDIN to Epoch (or excel) date

Hey William,

 

One thing you might also want to look into is a document we have:

Creating Human Readable Dates from SBS database timestamps in Excel

I hope that helps

 

Thanks,

Kevin

William England Novice
William England
99 posts since
Nov 18, 2008
Currently Being Moderated
Nov 2, 2009 9:37 AM in response to: Kevin Imber
Re: LASTLOGGEDIN to Epoch (or excel) date

That's perfect!  Thanks, Kevin - this document http://www.jivesoftware.com/jivespace/docs/DOC-5978 is precisely what I needed.

More Like This

  • Retrieving data ...

Bookmarked By (0)

To better serve our customers we have included functionality to automatically follow up on a case after it has been idle for more than 5 days, and then auto close after an additional 3 days of inactivity. Choose No to acknowledge that this case will remain idle for longer than 5 days.
Making cases public allows other customers to learn from the solution of the case. It can also be used to gain feedback from others in the community. Ask our Support Engineers for more info, but we encourage you to make your cases public.