Remote Database field convert to date format

Get Help
1

I set up a database with the remote database. All the data is transmitted correctly. The only problem is that I can not use the dates in the date field as filters because I can not translate them into the appropriate date format.

fields I want to use as a date filter are
start_datetime "22.02.2018 18:30:00"
end_datetime “23.02.2018 08:30:00”

My second problem is that when I set the Truncate Dataset option on the remote tab database connection advanced tab on a Daily basis, She is not refreshing.

If you want to examine the example I created, the name is nobet_remote. If I need to give a password, I can share the CMS password with you.
Thank you.

2

The truncate option is bugged in 1.8.7. There’s already a bug logged for that and it will be fixed in 1.8.8.

For your date formatting, you simply need to change the date format that you compare to those fields. You can get the current date and time expressed in that format easily.

3

I’ve never tried with Xibo, but a random hacky way to overcome date format would be to use a view on the database with the fields formatted the way you wanted them as a sort of translator. For end user reports or exports in other frameworks, I use views almost exclusively so that I can have anything formatted or calculated how I want without breaking other front or back-end applications.

4

Thank you very much for your response. I am not a very good
programmer, so I guess this topic is a bit difficult. There is always
an easier and more practical way.
When I use the str_to_data function and compare it with NOW (), the
result is 1 or 0. I guess I should work more on it. Thanks again

5

can you give an example of the query you are using and what you are trying to achieve?

6

Hello,
it is okey . great

7

Just a reminder…i’m not an expert, or know anything about JSON, so the below is only regarding MYSQL.

Using your date string and query examples, what if you changed your query to

“select * from dataset_2 where STR_TO_DATE(startdate, ‘%d.%m.%Y %H:%i:%S’) <=CURTIME()
and STR_TO_DATE(endDate, ‘%d.%m.%Y %H:%i:%S’)>=CURTIME()”

if you want the data retrieved shown as a datetime value, you would have to get rid of the * and list the fields like this:

“select id, title, STR_TO_DATE(startdate, ‘%d.%m.%Y %H:%i:%S’) as startdate, STR_TO_DATE(endDate, ‘%d.%m.%Y %H:%i:%S’) as endDate, nobet, enlem, boylam from dataset_2 where STR_TO_DATE(startdate, ‘%d.%m.%Y %H:%i:%S’) <=CURTIME()
and STR_TO_DATE(endDate, ‘%d.%m.%Y %H:%i:%S’)>=CURTIME()”

A massive amount of records would bring up the need to have a more efficient query.

1 Like
8

Thank you a lot it is work great.

9

I’ve had a screen that’s been working for a week.
At one point, the ticker region began to give the error message “unexpected
error please contact support”.
I tried to show the same information with Dataset module again I got the
same error.
I deleted the dataset table and recreated the problem was not resolved.
I’m sending the layout export file together.
I would really appreciate if you can help.the line I’m using for the filter
looks like this
ilce_id=127 and STR_TO_DATE(nobet_baslangic_tarihsaat, ‘%d.%m.%Y
%H:%i:%S’) <=CURTIME() and STR_TO_DATE(nobet_bitis_tarihsaat, ‘%d.%m.%Y
%H:%i:%S’)>=CURTIME()

10

I am very sad. I was giving an error because of a mistake. I fixed the
problem of typing the filter. I am so sorry