Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Archived » Eclipse Process Manager (Stardust) » Use of Timestamps in Stardust AuditTrail database(Retrieving and using timestamps in Stardust AuditTrail database)
Use of Timestamps in Stardust AuditTrail database [message #742083] Thu, 20 October 2011 06:45 Go to next message
Srinivasan Iyer is currently offline Srinivasan IyerFriend
Messages: 36
Registered: October 2011
Member
Stardust stores dates as long values in the Audit Trail. The statement below helps to check timestamps in a Stardust Audit Trail if Oracle DB is used.

e.g.

select to_char(to_date('01-JAN-1970','DD-MON-YYYY') + ( ai.starttime / (1000 * 60 * 60 * 24) ),'DD-MON-YYYY HH24:MI:SS') "STARTTIME" from activitiy_instance ai;

[Updated on: Thu, 20 October 2011 08:25]

Report message to a moderator

Re: Use of Timestamps in Stardust AuditTrail database [message #742084 is a reply to message #742083] Thu, 20 October 2011 06:45 Go to previous messageGo to next message
Srinivasan Iyer is currently offline Srinivasan IyerFriend
Messages: 36
Registered: October 2011
Member
Same SQL for MySQL databases looks like this ...

select cast(from_unixtime(ai.starttime / 1000) as datetime) from activity_instance ai


... considering timezone conversion ...


select convert_tz(cast(from_unixtime(ai.starttime / 1000) as datetime), '+00:00', '+05:00') from activity_instance ai

[Updated on: Thu, 20 October 2011 08:25]

Report message to a moderator

Re: Use of Timestamps in Stardust AuditTrail database [message #742086 is a reply to message #742084] Thu, 20 October 2011 06:47 Go to previous message
Srinivasan Iyer is currently offline Srinivasan IyerFriend
Messages: 36
Registered: October 2011
Member
In case you need to add a where predicate on a date field that is stored as long value, you can use something similar to the SQL statement below (Oracle)...

SELECT count(*)
FROM process_instance pi
WHERE pi.state = 2
AND pi.oid = pi.rootprocessinstance
AND pi.terminationtime < (to_date('2010-01-01', 'YYYY-MM-DD') - to_date('1970-01-01','YYYY-MM-DD')) * (24 * 60 * 60 * 1000)

[Updated on: Thu, 20 October 2011 08:26]

Report message to a moderator

Previous Topic:How to enable logging on transaction management for Spring managed Stardust runtime?
Next Topic:Resetting daemon status at the database level
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 11:26:27 GMT 2024

Powered by FUDForum. Page generated in 0.02286 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly