How to list all created team calendars for Confluence, their creators, and the creation date
This queries below will return 3 columns (creator, calendar_name and creation_date) for all calendars in the database. This data can be useful for reporting for all Team Calendars for Confluence, their creation dates, and creators for administrators.
For Confluence 5.3+ only
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;
Last modified on Jun 20, 2019
Powered by Confluence and Scroll Viewport.