How to Query Team Calendar Events by Date

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Platform Notice: Data Center Only - This article only applies to Atlassian products on the data center platform.

Purpose

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

(info) 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 

Click here to expand...
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

Click here to expand...
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

Click here to expand...
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

Click here to expand...
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:



DescriptionHow to Query Team Calendar Events by Date
ProductConfluence
Last modified on Sep 21, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.