Invalid time value error, Budibase version 2.29.14.

[dado-ca]

After upgrading Budibase,

one of my apps broke. It is showing time value error. I am using Postgres database.

Any way to correct this.

This is the table which is giving me issues.

CREATE TABLE IF NOT EXISTS public."tHE_TimeAttendance"
(
"anAttendanceId" bigint NOT NULL DEFAULT nextval('"tHE_TimeAttendance_anAttendanceId_seq"'::regclass),
"anEmployeeId" integer,
"acIntervalId" character(5) COLLATE pg_catalog."default",
"adDate" timestamp with time zone,
"adBeginTime" time with time zone,
"adEndTime" time with time zone,
"anDuration" numeric,
"acRecordLocked" character(5) COLLATE pg_catalog."default" DEFAULT 'F'::bpchar,
"acDayOfWeek" text COLLATE pg_catalog."default",
"acDepartmentId" character(5) COLLATE pg_catalog."default",
CONSTRAINT "tHE_TimeAttendance_pkey" PRIMARY KEY ("anAttendanceId"),
CONSTRAINT "tHE_TimeAttendance_acDepartmentId_fkey" FOREIGN KEY ("acDepartmentId")
REFERENCES public."tHE_Department" ("acDepartmentId") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT "tHE_TimeAttendance_acIntervalId_fkey" FOREIGN KEY ("acIntervalId")
REFERENCES public."tHE_Interval" ("acIntervalId") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT "tHE_TimeAttendance_anEmployeeId_fkey" FOREIGN KEY ("anEmployeeId")
REFERENCES public."tHE_Employee" ("anAutoId") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."tHE_TimeAttendance"
OWNER to metabase;

[mikesealey]

Hey @dado-ca - What version have you updated to? Does reverting to the previous version resolve the issue? I've seen this issue a couple of times before, and I know the engineering team have done a bunch of work surrounding this.

[dado-ca]

Upgraded to version 2.29.14. I have tried to revert to previous version 2.22.13, but no success.

[mikesealey]

Were you unable to revert to the previous version, or were you able to revert but it didn't solve the issue?

I'll dig into this a little more - I had assumed this was a solved issue.

[dado-ca]

I reverted the App to the previous version, but I get the same error. When I run Select in pgAdmin on the mentioned table, it works fine. All records are present.

[dado-ca]

Has anyone a suggestion how to solve this. I have the following date and time columns in the affected table:

How can I figure out which of those datatypes is giving me this error. I've wrote an sql query to get all rows from this table, and loaded it to the new table component via dataprovider. And it loads the data fine, without an issue.

I was hoping it will give me the same error, I then would select this date/time columns one by one by the query, so I would be able to pipoint the troublesome column.

But as I said, it loads data fine by using a query.

[dado-ca]

Not able to solve this. I have updated the Budibase to the latest version today, 2.30.3, but this issue still persists.

For testing purposes I Created one additional test table in Postgres containing one column of type "time with timezone" and one column of type "timestamp with timezone". I've added some test data and fetched the new table into my app. The data shows just fine without any problems.

But I,ve noticed something. When viewing the new table, the column of type "time with timezone" in the Data section of my app, I see this:

When doing the same on my table which has issues, the column of type "time with timzone" look like this:

For some reason, in the table which gives me issues, the column of type "time with timezone" is identified as timestamp.

🤔

[dado-ca]

So I have done this:

Created new app, and fetched all the tables.

Now when viewing the affected table, the columns od type "time with timezone" are correctly indetified as such.

So, the question is, what needs to be done, to get budibase identify this columns data types correctly?

[dado-ca]

I was able to solve the issue by dropping the "tHE_TimeAttendance" table, and recreating the same table. Then I fetched the table again inside budibase.

This time, budibase correctly recognizes the time only columns as such.

Fortunately, it was acceptable for me to truncate the table.

I think changes made in the date picker component triggered this issues. I had some other strange behaviour relating also to date and time based columns, but was able to solve them.