What is the MYSQL Version for Saviynt ver 5.4 onwards ?

[Community_User]

Originally posted on January 17 2020 at 17:29 UTC

What is the MYSQL version being used in Saviynt ver 5.4 onwards?

I am trying to use the FROM_UNIXTIME() function in an analytic report query to convert a timestamp into a date, but it is not working.

e.g.

SELECT trigger_name, NEXT_FIRE_TIME, FROM_UNIXTIME(NEXT_FIRE_TIME)

FROM qrtz_triggers Limit 2

So I think, I need to know which MYSQL version we are on. So that I can find the appropriate function to convert timestamp.

[Community_User]

Originally posted on January 17 2020 at 17:40 UTC

Anand,

You need do use the below format FROM_UNIXTIME(NEXT_FIRE_TIME/1000)

[Community_User]

Originally posted on January 17 2020 at 18:02 UTC

Since we have milliseconds as well you can try:

SELECT trigger_name, NEXT_FIRE_TIME, FROM_UNIXTIME(floor(NEXT_FIRE_TIME/10000))

[Community_User]

Originally posted on January 17 2020 at 18:06 UTC

Ignore my option, divide by 1000 is correct.

[Community_User]

Originally posted on January 17 2020 at 18:28 UTC

Harsh

the option with 1000 gives a blank value. : FROM_UNIXTIME(NEXT_FIRE_TIME/1000) AS 'NEXT'

the option with 10000 gives a date value but it does not seem to be correct. : FROM_UNIXTIME(NEXT_FIRE_TIME/10000) AS 'NEXT'

e.g.

NEXT_FIRE_TIME NEXT FIRE START_TIME START 4070945700000 1982-11-25 17:49:30.0 1564160374000 1974-12-16 08:53:57.4 4070945700000 1982-11-25 17:49:30.0 1568658540000 1974-12-21 13:50:54.0

[Community_User]

Originally posted on January 27 2020 at 19:19 UTC

Any idea how to get the correct date time in a MYSQL Query from the Saviynt Database?

[Community_User · Accepted solution]

Originally posted on February 11 2020 at 16:44 UTC

to find out the MySQL version, create a new SQL-based analytic and enter the following query:

SHOW VARIABLES LIKE "%version%";

[Community_User]

Originally posted on February 11 2020 at 18:24 UTC

Thank you Fabrice.