Working with datasets

Hi all!

I want to manually hide a row from a table created with dataset. Let’s say that I have a schedule for a week, and an information from dataset is displayed on the layout that is scheduled on monday, thursday and saturday. After a couple of hours some information that is showed on layout I don’t want to be displayed any more, and the unique solution that I know is to remove from dataset, but if I do this it will not be displayed any more on the next scheduled days.

Does someone has a solution for this? Thank you!

You’d need to create columns with dates (and time) and then use filter to only show specific rows. (ie from hour a to hour b on Monday/thursday/saturday). For rows that should be displayed all the time, perhaps leave some column empty and then add it to the filter.
So it will display specific rows as above and all rows that have column x empty (or just give them some long date in to the future)

If I put in a column that will keep only the time in this format hh:mm, let’s say 16:25, 18:00, could I compare the values from that column with NOW() or another function in the filter of the dataset?

Probably CURTIME() would be better

http://www.w3schools.com/sql/func_now.asp

It works fine if I want to compare CURTIME() with values from my column. But I would want to compare CURTIME() with values from my columns and to add a value to them. Let’s say that I have in a column 13:25 and I want to add 20 minutes to this value. Something like:

CURTIME() < (ColumnName + 900 seconds)

I tried many of this but I didn’t find something that works. Do you know the correct syntax or if is it possible what I am trying to do?

CURTME() < (ColumnName  + INTERVAL 900 SECOND)

so it should check if current time has lower value than whatever time you have in your column + 900s - if yes it will display it.

Thanks but unfortunately it doesn’t work. I have another question: could I use WHERE to filter a table?

try to use it with NOW() , column would need date in this format 2016-03-10 11:35:00
it should correctly add(+) or subtract(-) intervals from it.

you don’t need to, just type ColumnName = "text" in filter.
it will display all rows where value in that column is equal to text.

In my column the format of time that I use is hh:mm. If I compare this with CURTIME() works fine, but if I compare with NOW() it doesn’t work good. I want to use WHERE after another condition, for example: CURTIME() < ColumnName1 WHERE ColumnName2 = ‘text’.

Instead of WHERE, try &&

I have a dataset with just 2 columns EE Name and DOB.
I’m trying to figure out how to filter the birthdays so only the employees with birthdays this week show.
I started by using this SQL filter WEEK(Date) = WEEK(CURDATE()) AND MONTH(Date) = MONTH(CURDATE())
But for whatever reason it doesn’t show anything once I enter that filter. And I’ve got at least 6 people with birthdays this week.

Try placing DOB in your filter, like this

WEEK(DOB) = WEEK(CURDATE()) AND MONTH(DOB) = MONTH(CURDATE())

Today is March 10. I had data in my dataset for birthdays on these dates

2014-03-01
2014-03-06
2014-03-11
2014-03-16
2014-03-21
2014-03-26
2014-03-31

With the above filter my output was the name of the person with the birthday on March 11.

That was it thanks.
Back when I was testing I had date as the column header so that’s why it stopped working all of a sudden.
Thanks again RLaurette

Viper92, I’ve checked the source (file lib\data\dataset.data.class.php on the server). The following are blacklisted and will be stripped from the filter ‘;’, ‘INSERT’, ‘UPDATE’, ‘SELECT’, ‘DELETE’, ‘TRUNCATE’, ‘TABLE’, ‘FROM’, and ‘WHERE’.

I tried what you said RLaurette, but it doesn’t work how I would like. If I use && it executes both commands before and after &&, but I want the first command to be conditioned by the second. I need toi use something like IF but it isn’t available in SQL. Do you know if I can do this? Thank you very much!

I found what I was looking. I had to use “OR” and now works great :). Do you know if I can add a period of time to a column(datetime type) ? Something like: CURTIME() < ColumnName + 90 seconds? Thanks a lot!

Does Peter’s recommendation work for you?

I tried this but it doesn’t work.

I’ve created a test layout and test dataset in CMS 1.7.5. The dataset has two columns. In my column Col_TimeStamp, I placed dates in Y-m-d H:i:s format (i.e. 2016-03-21 09:44:00) that represent the event start times. In my other column, I placed my event names.

The following filters work for me on 1.7.5

to show only events that haven’t yet started
Col_TimeStamp > NOW()

to show only today’s events that haven’t yet started
Col_TimeStamp > NOW() AND DATE(Col_TimeStamp) = CURDATE()

to show only today’s events that will start at least two hours from now
Col_TimeStamp > DATE_ADD(NOW(),INTERVAL 2 HOUR) AND DATE(Col_TimeStamp) = CURDATE()

to show only today’s events that started less than 45 minutes ago or haven’t yet started
Col_TimeStamp > DATE_SUB(NOW(),INTERVAL 45 MINUTE) AND DATE(Col_TimeStamp) = CURDATE()

It worked. Thank you so much man!