How to Query Team Calendar Events by Date
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
As the filtering and search feature for Team Calendar events is currently not available through the UI just yet, we can query Team Calendar events in the database for reporting or related purposes. The following queries will allow you to search calendar events based on a date:
Solution
ℹ️ The query below uses '2021-07-15' as the search term (at the very end of the query), please change it to the date you're looking.
MySQL
1
2
3
4
5
6
7
8
9
SELECT AO_950DC3_TC_EVENTS.SUMMARY,
AO_950DC3_TC_EVENTS.DESCRIPTION,
FROM_UNIXTIME((AO_950DC3_TC_EVENTS.START/1000)),
FROM_UNIXTIME((AO_950DC3_TC_EVENTS.END/1000)),
AO_950DC3_TC_SUBCALS.NAME
FROM AO_950DC3_TC_EVENTS
JOIN AO_950DC3_TC_SUBCALS
ON AO_950DC3_TC_EVENTS.SUB_CALENDAR_ID = AO_950DC3_TC_SUBCALS.ID
WHERE FROM_UNIXTIME((AO_950DC3_TC_EVENTS.START/1000), '%Y-%m-%d') = '2021-07-15';Postgres
1
2
3
4
5
6
7
8
SELECT "AO_950DC3_TC_EVENTS"."SUMMARY",
"AO_950DC3_TC_EVENTS"."DESCRIPTION",
to_timestamp(("AO_950DC3_TC_EVENTS"."START"/1000)),
to_timestamp(("AO_950DC3_TC_EVENTS"."END"/1000)),
"AO_950DC3_TC_SUBCALS"."NAME"
FROM "AO_950DC3_TC_EVENTS" JOIN "AO_950DC3_TC_SUBCALS"
ON "AO_950DC3_TC_EVENTS"."SUB_CALENDAR_ID" = "AO_950DC3_TC_SUBCALS"."ID"
WHERE to_timestamp(("AO_950DC3_TC_EVENTS"."START"/1000)) ::date = '2021-07-15'Oracle
1
2
3
4
5
6
7
8
9
SELECT AO_950DC3_TC_EVENTS.SUMMARY,
AO_950DC3_TC_EVENTS.DESCRIPTION,
to_date('01.01.1970','dd.mm.yyyy') + to_number(AO_950DC3_TC_EVENTS."START")/1000/60/60/24,
to_date('01.01.1970','dd.mm.yyyy') + to_number(AO_950DC3_TC_EVENTS.END)/1000/60/60/24,
AO_950DC3_TC_SUBCALS.NAME
FROM AO_950DC3_TC_EVENTS
JOIN AO_950DC3_TC_SUBCALS
ON AO_950DC3_TC_EVENTS.SUB_CALENDAR_ID = AO_950DC3_TC_SUBCALS.ID
WHERE to_date('01.01.1970','dd.mm.yyyy') + to_number(AO_950DC3_TC_EVENTS."START")/1000/60/60/24 = DATE '2021-07-15';MS SQL
1
2
3
4
5
6
7
8
9
SELECT AO_950DC3_TC_EVENTS.SUMMARY,
AO_950DC3_TC_EVENTS.DESCRIPTION,
dateadd(second,cast(cast(AO_950DC3_TC_EVENTS."START" AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000'),
dateadd(second,cast(cast(AO_950DC3_TC_EVENTS."END" AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000'),
AO_950DC3_TC_SUBCALS.NAME
FROM AO_950DC3_TC_EVENTS
JOIN AO_950DC3_TC_SUBCALS
ON AO_950DC3_TC_EVENTS.SUB_CALENDAR_ID = AO_950DC3_TC_SUBCALS.ID
WHERE dateadd(second,cast(cast(AO_950DC3_TC_EVENTS."START" AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000') = '2021-07-15';Feature Request
Your feedback and voice are highly appreciated, please feel free to add a comment or engage in the conversation in the suggestion ticket below:
Was this helpful?