Any pitfalls in using Text field types for storing dates in bubble data tables?

[nam1]

Hello,

I’m new to bubble but not new to programming. We’ve contract a team to develop an app for us in bubble and they’ve been really good. The one thing that I don’t agree with is that for some reason they have used Text data field types to store Timestamp data in a few tables. There are just a few fields this is happening with and it makes me EXTREMELY uncomfortable having lived through and fixed code during Y2K that would have killed children left unfixed.

I first noticed this during testing when I saw the date and time of a transaction that was in the future which should not happen so I started to dig around.

My questions are:

1. What are the pitfalls in using text fields for date storage in bubble? I saw that bubble has also changed the way they convert and add months given leap years, February, time zones, and daylight savings time.
2. Do date conversion/manipulation functions in bubble require datetypes or will they take text datatype or would you just convert the text to a date datatype and use it to call the function?
3. They are now going to store all of the dates as UTC in the text fields and do all of the conversions. I’m not really sure about the thinking behind this.
4. For sure looking at the raw data in the database will be less convenient because bubble automatically converts date fields to local time before displaying them. When looking at these text fields, I would need to convert them in my head.

Any comments would be great appreciated.

I do like their work so far and am very happy with what they have done…except for this. Let me know if I’m over thinking it.

Thanks.

[boston85719]

Just replying based on the title of the post.

Main pitfall of this is that the date would no longer be a data data type and you would lose all functionality for manipulating the date data type.

@nam1

Do date conversion/manipulation functions in bubble require datetypes or will they take text datatype or would you just convert the text to a date datatype and use it to call the function?

The data operators available through dynamic expressions are limited to the type of data it is. Text data types only have operators available for text data types, number data types have number operators etc. So yes, definitely lose the conversion/manipulation functions.

@nam1

They are now going to store all of the dates as UTC in the text fields and do all of the conversions. I’m not really sure about the thinking behind this.

Quite possibly they have some reason that makes sense to them. It doesn’t make sense to me personally, and I would never store a date as a text field, as there is just no reason to do it in my experience. Date data types are already stored as UTC anyway.

@nam1

For sure looking at the raw data in the database will be less convenient because bubble automatically converts date fields to local time before displaying them. When looking at these text fields, I would need to convert them in my head.

Yes, you lose all functionality associated with date data types.

@nam1

I do like their work so far and am very happy with what they have done…except for this. Let me know if I’m over thinking it.

I would press them on the rational behind their approach. If you are not satisfied with it, then you should insist they save it as dates. After all, it is your application they are building for you as the paying client. In most instances the client may not know as much as the developer they hire, but it never hurts to have discussion with the developer and push for clarification.

[nam1]

Thanks @boston85719 ! Yea, I always stored dates in date datatypes unless there was something that required it.

Regarding date operators, does bubble have a way to declare a variable a date datatype which would allow you to convert other datatypes to a date to take advantage of those operators? Most traditional coding languages have a way to do this.

I’m not suggesting doing this, but worse case, could a field of date datatype be created in a data table and used to temporarily store a text data value. It would then be a date type and you would be able to manipulate as a date? I know, it’s a lot of work around.

[sudsy]

@nam1

for some reason they have used Text data field types to store Timestamp data in a few tables.

Did you ask them what their reason was? There might be valid reasons for doing so under certain circumstances, although I would imagine they’re few and far between.

If you can post the rationale they provide, then perhaps others can suggest alternative approaches which preserve the date data type. Or they might really know their stuff and are employing some advanced optimization strategy or something.

[nam1]

Thanks!

That’s the first thing I asked. For one of the fields it makes some sense. It is not used as a timestamp, but as a way to filter results. We needed a way to filter one day’s worth of records. The way they implemented that was to create a new field that just has the date with no time so that we could use that for the filter selection option. I think this is fine and the field should not be used as a timestamp. There are two other fields are timestamps and they never gave me an answer as to why those two are being stored as text. I just sent them a message specifically about those.

[sudsy]

@nam1

For one of the fields it makes some sense. […] We needed a way to filter one day’s worth of records.

Interestingly, that’s not a use-case that I would think warrants a textual representation of a date. I see no reason at all why a date data type couldn’t be used for that.

I think you’re right to raise those questions. Hopefully, they can justify their decisions.

[boston85719]

@nam1

We needed a way to filter one day’s worth of records

For that there are possible ways using conditionals and/or operators to achieve while using a date data type. Using the extract method for extracting different components or by using an operator to transform it from date data type to text data type by way of the :formatted as operator.

[nam1]

Here’s a screenshot of the filter. We wanted a quick way to get a particular day’s worth of records. Prior to Nov 8 when the change was made, we would see all of the different records individually. I’m actually OK with the filter date being used and it being a text field for this purpose. It’s maybe not the most elegant or efficient way to do it but it also doesn’t increase any risk of errors. It’s the other uses that I have heartburn about.

[boston85719]

@nam1

Regarding date operators, does bubble have a way to declare a variable a date datatype which would allow you to convert other datatypes to a date to take advantage of those operators?

Only way that I can see that would work is when using the Simplified Extended ISO value in a URL parameter that when you extract it from the URL you declare it as a date.

[boston85719]

Whatever the datasource is for that list I think you could, if it was dates, put the operator of unique elements to make it only show the date once if there are any records for that date.

I did that in my Calendly Clone template for showing a list of meetings on a single day.

[lindsay_knowcode]

This is a quality discussion. There are times where you have to deviate from being a purist with Bubble, so things work in a Bubblish way. I get the requirement for fast filtering etc, but I can imagine you will quickly need a Date not a String - eg “Between two dates”. (and then there will be a lot of expensive type conversion at runtime required)

There are times I’ve stored the same field twice in two formats (date, text) so I can use it as a Text type with plugins (charts, tables that need Text or numbers for sorting) for example, and managed to put my purist ideals aside

[nam1]

@boston85719 Thanks! If you had a text input box that asks the user to type in their birth date. Is it possible to take that text string and store it in a database table field that is a date datatype? Once you have done that, you would have a date type that can use date operations. Am I missing something? This is more for my learning of bubble than the original question and it will help me to learn the “bubble way”. Definitely not how I would do it, but could it be done?

[nam1]

Thanks for all of the replies, folks. So the general thought on using text fields for dates?

1. Hell no

2. It could work but too much risk for programming errors, wasted processing cycles, etc.

3. its not a big deal and you can get around it.

[sudsy]

@nam1

Here’s a screenshot of the filter.

How is the lower bound determined for the range of dates to display for the filter? If unbounded, the list will obviously grow insanely large over time, resulting in a poor UX.

I find myself wondering why a small calendar popup isn’t used to allow the user to choose a date. If a date with no records is picked, an appropriate message could be displayed.

And if a dropdown is part of the UI requirement but there’s never a need to look more than x days/months into the past, the whole thing could be made fast and dynamic with a configurable “look-back” period (while filtering out the weekends if desired).

Or is showing a list of only days having records an important part of the requirement?

[nam1]

Nice ideas. I’ll need to ponder on it some. Thanks!

[sudsy]

@sudsy

Or is showing a list of only days having records an important part of the requirement?

BTW, this is also totally possible using the native date type. There’s just slightly more to it than a simple list of consecutive days or weekdays. (Still better than the needless bloat and complexity of a separate text field though, IMO.)

Anyway, good luck!

[boston85719]

@nam1

This is more for my learning of bubble than the original question and it will help me to learn the “bubble way”

One of the things that helped me the most with learning Bubble is testing things out on my own to see how they may or may not work. If what I thought should work doesn’t I look toward the forum for more information on how to do it by searching and reading through forum posts on the topic.

If after reading through things, it seems like I should be able to do what my tests indicate is not possible I reach out to Bubble support inquiring if it is a Bug or expected behavior.

@nam1

If you had a text input box that asks the user to type in their birth date. Is it possible to take that text string and store it in a database table field that is a date datatype?

Give it a test…personally all I would be doing to answer that question for you is testing it myself to report back to you…I think you’d be able to eliminate the middle man and test it out first to see if it is possible or not.

[nam1]

Just an update, we ended up doing away with those fields altogether and using the Created Date and Modified Date. Thanks All!