How to display timestamp

22 views
Skip to first unread message

JRGabelo

unread,
May 15, 2019, 2:01:59 AM5/15/19
to schedulix
Hi - 

I just wanna ask how can we display the proper format of timestamp as we are seeing series of digits in querying values of start_ts, finish_ts and other columns that has _ts from database tables.

Am I looking at the wrong column? Our goal is just to identity the timestamp of a submitted entity in database. Attached is a sample from submitted_entity table.


Thanks,
- JR







time.PNG

Dieter Stubler

unread,
May 15, 2019, 3:47:50 AM5/15/19
to schedulix
Hi,
Timestamps are in tables are encoded values.
Use the SCI_... Views to get database dates and time as well es decoded numeric values.
Regards
Dieter

JRGabelo

unread,
May 15, 2019, 6:01:23 AM5/15/19
to schedulix
Thank you Dieter! :)

Pablo Maldonado

unread,
May 15, 2019, 10:37:19 AM5/15/19
to schedulix
Dieter,

Is it possible to get the timestamp with something like this?

SELECT unixtime(sub.submit_ts/1000, '%d/%m/%Y %H:%i:%s') as fecha
FROM    SUBMITTED_ENTITY  sub;


Pablo Maldonado

Dieter Stubler

unread,
May 15, 2019, 11:38:52 AM5/15/19
to schedulix
Hi Pablo,

Depends on your database system.
Timestamps need additional dencoding of a bit set to distinguish timestamps from time intervals internally.
Just have a look at the SCI_ views.
You can find them in the table creation scripts for your database vor example for mysql/mariadb in $BICSUITEHOME/sql/mysql_gen/EXIT_STATE_DEFINTION.SQL:

....
CREATE VIEW SCI_C_DEPENDENCY_DEFINITION AS
SELECT
    ID
    , `SE_DEPENDENT_ID`              AS `SE_DEPENDENT_ID`
    , `SE_REQUIRED_ID`               AS `SE_REQUIRED_ID`
    , `NAME`                         AS `NAME`
    , CASE `UNRESOLVED_HANDLING` WHEN 1 THEN 'IGNORE' WHEN 2 THEN 'ERROR' WHEN 3 THEN 'SUSPEND' WHEN 4 THEN 'DEFER' WHEN 5 THEN 'DEFER_IGNORE' END AS `UNRESOLVED_HANDLING`
    , CASE `DMODE` WHEN 1 THEN 'ALL_FINAL' WHEN 2 THEN 'JOB_FINAL' END AS `DMODE`
    , CASE `STATE_SELECTION` WHEN 0 THEN 'FINAL' WHEN 1 THEN 'ALL_REACHABLE' WHEN 2 THEN 'UNREACHABLE' WHEN 3 THEN 'DEFAULT' END AS `STATE_SELECTION`
    , `CONDITION`                    AS `CONDITION`
    , CASE `RESOLVE_MODE` WHEN 0 THEN 'INTERNAL' WHEN 1 THEN 'EXTERNAL' WHEN 2 THEN 'BOTH' END AS `RESOLVE_MODE`
    , `EXPIRED_AMOUNT`               AS `EXPIRED_AMOUNT`
    , CASE `EXPIRED_BASE` WHEN 0 THEN 'MINUTE' WHEN 1 THEN 'HOUR' WHEN 2 THEN 'DAY' WHEN 3 THEN 'WEEK' WHEN 4 THEN 'MONTH' WHEN 5 THEN 'YEAR' END AS `EXPIRED_BASE`
    , `SELECT_CONDITION`             AS `SELECT_CONDITION`
    , `CREATOR_U_ID`                 AS `CREATOR_U_ID`
    , from_unixtime((`CREATE_TS` & ~1125899906842624)/1000) AS `CREATE_TS`
    , `CHANGER_U_ID`                 AS `CHANGER_U_ID`
    , from_unixtime((`CHANGE_TS` & ~1125899906842624)/1000) AS `CHANGE_TS`
  FROM DEPENDENCY_DEFINITION
 WHERE VALID_TO = 9223372036854775807;
...

Hope that helps

Regards
Dieter

Reply all
Reply to author
Forward
0 new messages