Format as Date for Dataset

[justinedwards.jle]

I believe your query is returning the correct format now. Perhaps, the datatype for the columns has been corrupted somehow by the previous string formats. Perhaps recreating the calendar, and using the current query will fix it.

[pturmel]

Don’t format the dates as strings. The only reliable way to deliver data to the calendar is to deliver the actual date datatype.

[justinedwards.jle]

Okay, fresh day and fresh set of eyes.

I went ahead and set up a mySQL database with the following data:

I can pull the data in with no formatting whatsoever, and the calendar is perfectly happy:

SELECT MIN(fichajes.inicio), 
	MAX(fichajes.fin) as startDate, 
	"blue" as displayColor, 
	QR as display 
FROM fichajes;

so I just have to make sure: Are the datatypes of your SQL database datetimes?

Edit: I just noticed that I messed up the aliases for the start and end date. Nevertheless, the calendar doesn’t seem to care. It still displays the event as long as the first two columns are datetime datatypes. @OscarLucas the problem you are experiencing has to be a datatyping issue

[pturmel]

Right, the key is that the JDBC driver must map the column type to java.util.Date, the milliseconds-UTC-based object Ignition uses all over for its DateTime values. (java.sql.Timestamp is a compatible subclass.)

For SQL Server, a DATETIME column maps appropriately, but assumes the timezone of the DB connections. A DATETIME2 column maps with timezone information, but is overconstrained for java.util.Date. Haven’t played with it lately, but early JDBC drivers would just puke on it.

For MySQL or MariaDB, a DATETIME column behaves like MS SQL, and a TIMESTAMP column retains timezone information.

For PostgreSQL, a timestamp column behaves like an MS SQL DATETIME, while a timestamptz column retains timezone information.

[justinedwards.jle]

@pturmel
This is interesting information. I was having trouble understanding why DATETIME formatting would ever be internally necessary, other than for display purposes, and the experiment conclusively shows that [in this case] it’s not, so just to make sure I’m clearly understanding you, you’re suggesting that this problem could be a JDBC driver issue that is related to the timezone he is in?

[pturmel]

Possibly. In MySQL, I recommend the TIMESTAMP column type. It maps 1:1 exactly with java.util.Date, preserving the UTC milliseconds in both directions, even with time zone changes.

[OscarLucas]

I finally got the result.
Reading your conversation I understood that the problem was formatting in the database.
Changing the format solved the problem.
I thank you very much for your help. Thanks a lot.
I attach the configuration so that other users can use it.