Confluence で作成されたすべてのチーム カレンダー、それらの作成者、および作成日の一覧を作成する方法
以降のクエリはデータベース内のすべてのカレンダーについて 3 つの列 (creator、calendar_name、creation_date) を返します。このデータは、すべての Team Calendars for Confluence、それらの作成日、および作成者を管理者に報告するのに便利です。
Confluence 5.3+ のみ
SQL Server
SELECT um.lower_username as creator, tc.NAME as calendar_name, dateadd(second,cast(cast(tc.CREATED AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000') as creation_date
FROM AO_950DC3_TC_SUBCALS tc
JOIN user_mapping um ON um.user_key = tc.CREATOR
WHERE tc.PARENT_ID IS NULL
ORDER BY creator;
MySQL
SELECT um.lower_username as creator, tc.NAME as calendar_name, from_unixtime(round(tc.created/1000)) as creation_date
FROM AO_950DC3_TC_SUBCALS tc
JOIN user_mapping um ON um.user_key = tc.CREATOR
WHERE tc.PARENT_ID IS NULL
ORDER BY creator;
Postgres
SELECT um.lower_username as creator, tc."NAME" as calendar_name, to_timestamp(CAST(tc."CREATED" AS bigint)/1000) as creation_date
FROM "AO_950DC3_TC_SUBCALS" tc
JOIN user_mapping um ON um.user_key = tc."CREATOR"
WHERE tc."PARENT_ID" IS NULL
ORDER BY creator;
Oracle
SELECT um.lower_username as creator, tc.NAME as calendar_name, to_date('01.01.1970','dd.mm.yyyy') + to_number(tc.created)/1000/60/60/24 AS creation_date
FROM AO_950DC3_TC_SUBCALS tc
JOIN user_mapping um ON um.user_key = tc.CREATOR
WHERE tc.PARENT_ID IS NULL
ORDER BY creator;
最終更新日 2019 年 6 月 20 日
Powered by Confluence and Scroll Viewport.