Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,299,502
Community Members
 
Community Events
165
Community Groups

Table Transformer: How to get all rows within a certain time range starting from today?

Hi,

I have a table with public holidays for the next couple of years. As I want to display future public holidays for a certain time range only, I'm trying (and crying) to create a SELECT statement which returns all dates from today to six months in the future.

With this SELECT I can display all rows from today:

SELECT
CASE
WHEN 6 - WEEKDAY('date') = 2 OR WEEKDAY('date') - 1 = 1 THEN CONCAT('name', " + ", "Brückentag")
ELSE name
END AS 'Feiertag', 'date' AS 'Datum', 'scope' AS 'Geltungsbereich', WEEKDAY('date') AS 'Wochentag'
FROM T1
WHERE CAST('date' AS DATE) > GETDATE()
ORDER BY 'date' ASC

To get just the dates for the coming six months, I tried, amongst others, the following solutions which all returned 0 rows:

  • WHERE CAST('date' AS DATE) BETWEEN GETDATE() AND DATEADD(month, -6, getdate())
  • WHERE CAST('date' AS DATE) BETWEEN GETDATE() AND DATE_ADD(GETDATE(), INTERVAL 6 MONTH)
  • WHERE 'date' < DATEADD(month, 6, GETDATE())
  • WHERE CAST('date' AS DATE) < DATEADD(month, 6, GETDATE())

I also tried to find all rows until the end of the current year but this only worked with a hardcoded "2023-01-01".

Any ideas for an nice solution?

Thanks,

Eishle

1 answer

1 accepted

Hi @Eishle ,

I can suggest using the Table Filter macro for the case:

Mon 6-1.png

Mon 6-2.png

Make sure that your date format corresponds the format that you are using in your source table:

Mon 6-3.png

You can use various expressions (-6m, 1w, today, etc.) and hide the filtration panel if necessary.

If your original case is a more complex one and you still need help, please raise a support ticket. The portal is confidential, so provide us with the screenshots of the source tables (with visible headers) and macro settings and we'll try to help you.

Hi @Katerina Kovriga _Stiltsoft_,

Thanks for your quick reply. If I set the range with this macro, would it serve as a "preset" for other page members or would they need to set it by themselves?

The use case is not complicated. That's why I was surprised that my solution didn't work.

At the moment, I use "LIMIT" in combination with "WHERE CAST('date' AS DATE) > GETDATE()" which is not perfect but comes quite close to the wanted effect.

If you save this filter, it will be set for all the page visitors (it works like any other simple dropdown or date range filter).

The "3m" and other dynamic filters were introduced specifically for the cases that are similar to yours (when there are manually created and automatically generated by the Page Properties Report, Jira Issues, etc. tables that need to show up-to-date information without any routine manual updates).

And what concerns the Table Transformer macro, it is based on the AlaSQL library. So some functions may be limited and require a workaround.

Thanks. I tried it and eventually saw that the table will be wrapped by a filter macro after clicking the buttons in the page view mode.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

283 views 2 5
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you