Data type "time" when empty cannot be inserted into supabase as null value

  • Goal: Insert two sets of opening and closing hours for a business into supabase. The first set is mandatory, the second set is optional.

  • Issue: The first set inserts correctly into supabase with the right business_id from a previous query. But the second set, when empty, does not return the correct null value that supabase expects for the type "time".

My question is: What do I do to my query - or elsewhere - such that the correct null value is passed to supabase?




Thank you! Really appreciate your help. I'm very new to this.

Hello!

You might be able to make use of an inline ternary operation to send a valid NULL value instead of the null string "". These take the form of {{ condition ? result if true : result if false }}. In your case, for each of the secondary values you can update with the following:

{{monday_secondary_open.value}} ->
{{monday_secondary_open.value == "" ? NULL : monday_secondary_open.value}}

and

{{monday_secondary_close.value}} ->
{{monday_secondary_close.value == "" ? NULL : monday_secondary_close.value}}

HI there @Andreasms,

Yeah this is a classic for Retool. The way I solve it is with this:

{{ monday_primary_open.value?.trim() != "" && monday_primary_open.value != "Invalid date" ? monday_primary_open.value : null }}

I found this solution in the forum, but can't find that post.

Hey @pyrrho and @MiguelOrtiz !
Thank you so much for your replies.

I obviously tried both and they both work! They both return correct NULL values and correct values when the secondary hours have hours in them.

The version by pyrrho looks like it wont work (see image) but that's only when there is no value inputted into the time field and it has to return NULL. Soon as I enter a time, it turns green. But it works as intended in both cases.

image

I can't judge if one way is better than the other or they just both happen to work for my case but might not in others, but now you each got a second option :smiley: Thank you again, really really appreciate it!

2 Likes

just a small suggestion, if you don't need to remove the space character I'd suggest using monday_primary_open.value?.length > 0 instead since the function has a linear time complexity or O(n) where as reading .length is constant or O(1)

another version is bellow, if you're not worried about speed/efficiency or need to cover as many fringe cases as possible, it returns true if a value is null, undefined, an empty str or consists of white space and line terminators only. similar to what @MiguelOrtiz gave :
{{ (monday_primary_open.value == null || (typeof monday_primary_open.value === "string" && monday_primary_open.trim().length === 0))? null : monday_primary_open.value }}

little side note - by comparing .trim().length with 0 instead of .trim() with "" we can save a smiget of time :saluting_face:. we're also making use of short circuiting here by using || which in cases where .value is null saves a bunch of time by skipping the call to .trim()

heh, I'm starting to wonder if I'm following you around @MiguelOrtiz without knowing it :smile:

1 Like

So would the edited version of his solution then be as follows?:

{{ monday_primary_open.value?.length > 0 && monday_primary_open.value != "Invalid date" ? monday_primary_open.value : null }}

Do you have any comment on that versus the solution provided by pyrrho? His solution seems less complicated than this version with the if true statement? I suppose since they both work its a matter of difference in speed and perhaps, as you write, covering fringe cases?

Thank you :slight_smile:

heh, @pyrrho def wins as far as speed goes. the code is a little deceiving though as it's hiding an odd part of javascript, implicit type coercion. js is able to take the left hand side of a comparison when it uses == and cast or convert it to the expected type on the right hand side of the operator.

in his code you see monday_secondary_close.value == "". if monday_secondary_close.value is a string, the comparison evaluates to: string == string. if it isn't a string, lets say it's the number 42, it initally evalutes to number == string at which point the compiler sees == instead of === and it knows it's allowed to try and force this to be a valid statement (valid in this case implies that both sides have the same type... it's difficult and sometimes impossible to know if the number 42 is equal to an empty string ""). behind the scenes it takes 42 and calls .toString() on it, resulting in our comparison now being valid but we just compared a number to a string. this might not be intended, what if I wanted to do something specific with numbers and something else with strings, we now have a problem. this 1 line of code also uses another somewhat hidden feature of javascript, "falsy" values. the values undefined, null, false, +/-0, NaN, "" will all evaluate to false if coerced to a boolean. so back to where we were, if monday_secondary_open.value is false instead of a string then the comparison is false == "" which is coerced to false == false and this evaluates to true.

in short, if you absolutely know 100% monday_secondary_close.value can only be a string, null or undefined then pyrrhos solution would probably be the best choice. if it's value is read from an outside source like an api or db not maintained and used by you exclusively (ie invalid/eronious data won't or can't be entered by others) there is the possibility that this could fail to produce the expected results without giving an error.

@MiguelOrtiz's solution takes it a step further and validates the contents as well as the types for null/undefined and empty strings. it does not check if the variable actually contains a string, as such the use != instead of !== can again result in implicit coercion and giving it an unexpected type can result in the same problem above. the odds are less likely with the extra checks and most likely this would be from reading improper data.

the other alternative solution I showed is going to be the safest. it ensures only a string is considered so you can't accidently trick it with weird/eronious data. in the worst case, it's also the slowest. in the case where monday_primary_open.value is null, it's the fastest however since only the 1st comparison is ever evaluated.

a general tip, if you want to ignore coercion/casting just always use ===, !== and such instead of ==. you usually will have to write out more conditions to cover the other types, but at least you know they're covered since you typed it out explicitly instead of letting the language check types that you didn't expect.

edit: javascript isn't my main language, so if anybody sees something wrong or understands something in a different/conflicting way please don't hesitate to correct me. its only too late to get facts straight when you give up learning... or when your in court :rofl: :innocent:

2 Likes

To add a little point about the issue with the NULL linter warning: I think NULL is being problematic because null is the protected keyword and it is case sensitive. If you replace those you might not see the "NULL is undefined" error where it thinks NULL is supposed to be a reference to a variable but since the variable doesn't exist it gives you back a null coincidentally.

2 Likes

And due to a lack of appropriate emojis I'll keep responding OH THATS FIRE!...

1 Like

@MiguelOrtiz :rofl: I sounded like a psycho at work laughing at my screen while typing that... but ya, this comment is in place of reacting with a :laughing: emoji. I'm tempted to start replying to my own thread with links to these posts that could be replaced in full with 1 emoji :smiling_imp:

feel free to tag us if you have any more questions, want more visual examples, or for anything else @Andreasms

1 Like