Date only (not time), into the database

jasonturo · 2021-01-23T23:36:58.513Z

Hi Bubble Community,

How is it possible to write into the database only the current date , excluding the time? mm/dd/yyyy

Thank you so much!
Jason

lakossaack · 2021-01-24T00:20:15.123Z

If the field is type Date, you cannot exclude the time. Which should be fine because when you go to display the date within your app you can choose the way to format the date.

adamhholmes · 2021-01-24T00:32:48.416Z

The ‘Date’ type in Bubble records a precise moment in time (to the millisecond).

So the ‘current date/time’ is effectively a ‘time-stamp’ of the moment it is created, to the nearest millisecond - that’s how it is recorded in the database.

From that recorded ‘moment’ you can extract as much, or as little, data as you like.

So if you only want the minute/hour/day/month/year etc. you can access that easily from that data when you reference or format it, either in the browser, or in a backend workflow.

jasonturo · 2021-01-24T01:23:08.899Z

Thanks, @adamhholmes and @lakossaack .

Adam, I apologize in advance if this question below posses any confusion with duplication of asking the same question. Earlier I referenced you in a previous thread from a couple days ago while working on date/time questions.

Does your above response (“that’s how it is recorded in the database.”) mean it is not possible to record the date into the database without the time? If it is possible what would be the best approach (extracting just the date somehow)? ie. What type of element, or workflow is best to use?

Presently the app’s ‘Current date/time is changing hours, minutes and seconds to 0’ to reference a common time of Today at 12am. However, there is a single user in another timezone that is recording into the database as 11pm (not sure if it’s because of the timezone or another reason yet), and I am attempting to possibly correct for that by eliminating the time. Unless there is another approach to take?

Thank you so much for the help!
Jason

cmarchan · 2021-01-24T04:10:26.346Z

As @adamhholmes said dates are recorded to the millisecond. Bubble uses the user’s browser time. This is the way it works.

Perhaps you can capture the date and for the purposes of “date math” you can use date operators. A useful one to use dates starting at 00:00 time on that day is :rounded down.

No matter what you do the date will be captured to the millisecond for that user in another TZ at his browsers time. But when you use that date info you can use workflows, conditions and display elements with date operators and formatting to display and/or use dates as you need them.

Hope this makes sense.

Below a quick link to a bubbles date operators info:

Bubble

Bubble Reference

Full reference for each Bubble element, feature, plugin. Bubble is a visual programing language. Instead of typing code, use a visual editor to build applications.

jasonturo · 2021-01-24T05:04:10.329Z

Thank you, Carlos. I’ll have to think about how to apply this.

We as humans do a great job at “over thinking it” and making a fairly simple problem way more complex than it needs to be. I am certainly a student and it’s safe to say, even a master of that!

Desired outcome:
I am simply attempting to write in things into the db on each day and then display all of them together by each day. Since I do not know the exact moment a user will input the data, I figured having the data recorded as a common time of 00:00:00 would be most systematic. However, I have verified from two people in another timezone other than me (both of them are in the same TM), their data records as 23:00:00 for some reason I cannot determine. Even when the time is converted to 00:00:00. I am not sure why being in another timezone records in this way, but it was replicated.

Suppose I could simply display the range based on created date 00:00:00 to 23:59:59 and not even create another field to change the time to at recording moment.

Recording & recalling all of a specific day’s data across 24 times zones is obviously basic functionality bubble can handle somehow. I am sure being a master “over complicating it” has come through yet again! Any suggestions?

Thank you so much.

cmarchan · 2021-01-24T05:29:03.592Z

@keith you are an expert in many things Bubble … and one of them is what I call “dates math”. I remember watching one of your videos where you explain the predicament of managing dates across time zones. If possible, could you post a link to that video?

keith · 2021-01-24T05:47:52.945Z

You’re probably looking for this:

Live Bubbling with Keith: Let's Talk About Dates - Explaining the Basics of Dates in Bubble Tips

Been a while since I posted a long-form video… Here’s a new 1 hour video where I talk about the basics of the much-misunderstood Date data type in Bubble. Covering what dates are and why we need them, explaining that dates are times, explaining the difference between date construction and date formatting, and showing the new “Utility” features in Calendar Grid Pro. This might be helpful to anyone who has struggled with dates or who wants a better understanding of the basics of dates. Also, I n…

jasonturo · 2021-01-24T15:21:47.029Z

Thank you, @cmarchan & @keith.

Keith is there a common way to display a day’s things? Look forward to watching the video you provided in an attempt figure this out.

Thank you so much,
Jason

adamhholmes · 2021-01-24T15:50:00.136Z

If it is possible what would be the best approach (extracting just the date somehow)?

If you mean to display it in the browser, you just reference the stored ‘date’ field and format it however you want to (e.g. Sunday 24th January, or 24/01/2021 etc) You can also choose which timezone to display it in…

If you mean you want to display records from a certain date (i.e. those created today) then just filter the records so that the ‘created date’ field’s value is greater than the current date/time with the seconds, minutes, and hours changed to 0 (in other words, 00:00 - midnight today).

Presently the app’s ‘Current date/time is changing hours, minutes and seconds to 0’ to reference a common time of Today at 12am. However, there is a single user in another timezone that is recording into the database as 11pm (not sure if it’s because of the timezone or another reason yet), and I am attempting to possibly correct for that by eliminating the time. Unless there is another approach to take?

How are you setting the ‘date’ field in the first place? Assuming it’s done in a browser workflow it will be set in the user’s timezone, so if you’re viewing it in a different timezone you’ll see a different time if it’s formatted in a readable way like 10:55am (although the actual recorded moment is the same). If you change the input time to midnight (or anything else), again that will be midnight in the user’s timezone, so when viewing that date in another timezone it will appear different if you’re formatting it as a readable date.

Check the Bubble reference for a more detailed explanation of how the ‘date’ type works in Bubble:

‘Date’ Type Reference | Bubble

keith · 2021-01-25T17:42:46.225Z

See: Constrain a list of items created today

jasonturo · 2021-01-26T02:47:15.308Z

Thank you, @keith and @adamhholmes.

I am in an attempt to make my data parallel across all users in any location. Only the users in my timezone are able to record into a date field as ‘00:00:00 today’. I have one other user who is in another timezone and his records as ‘23:00:00 yesterday’! I have no idea why his is different or how to make them all record as 00:00:00 today regardless of location.

Keith, I watched your video you referenced, however, I’m not sure the answer is in there?

Basically trying to record things for today or for tomorrow (that will be “today” when “tomorrow” comes). Having a date the thing is linked to seems simple in order to retrieve, however, it is not being recorded as ‘00:00:00 today’, for everyone. Any direction you could point me?

So greatly appreciate all your help.

Very best,
Jason

keith · 2021-01-26T04:26:25.019Z

jasonturo:

I have one other user who is in another timezone and his records as ‘23:00:00 yesterday’!

You realize that, of course, the start of today in any timezone is (nearly) always 23:00 hours yesterday in the timezone immediately to the West, do you not?

(I say “nearly” only because of DST-type adjustments.)

jasonturo:

Keith, I watched your video you referenced, however, I’m not sure the answer is in there?

You may have fallen asleep (who can blame you) during the part where I explained that dates represent a specific moment in time and that there is no such thing (web-wise) of a date without a time – or of a time without a date. DATES ARE TIMES: Specific moments in time, accurate to 1ms.

It sounds like you might have also missed the explanation of the differences between date construction and date formatting.

You might watch my overly-long video again with that info in mind, and then read this: https://moment.github.io/luxon/docs/manual/zones (at least, the first part of it, which is very much like how I explain these issues, but you might find more grokable than my presentation of it).

But on to what it is that you seem to be trying to do:

What you’re attempting to do (probably unnecessarily, mind you) is to implement a style of date recording commonly called “floating” dates. “Floating dates” are a concept where you attempt to throw out any differences between timezones and just say that (for example) 4 PM on 1/26/2021 just means 4PM wherever the user happens to be (or, more precisely actually, “in the user’s head”).

A common example of this approach:

We see this sort of system used in vacation rental systems (e.g., VRBO, Airbnb, etc.) where these systems publish ical feeds that publish dates essentially without timezone information. A user (who might live anywhere on the globe) books a property from some-check-in-date to some-check-out-date. The check-in / check-out dates are assumed to be in the property’s location and both the guest and the owner (both of whom who may not even in the same timezone as the property, of course) are presented these dates as exactly the same.

Like:

Guest, you will check in at Casa Fundido on 1/26/21 and check out on 2/1/21.
Owner, Guest will check in at Casa Fundido on 1/26/21 and check out on 2/1/21.

… even though the starting moment of 1/26/21 and 2/1/21 may in fact be different calendar dates for either the Owner or the Guest. (That is, the guest may in fact be checking in “yesterday” from the owner’s perspective, depending upon the relative position of the owner and the property, as just one example.)

They just handwave it away. A calendar date is the same for all users.

How does that work? How would you build such a system?

Especially if you understand how dates (by which I mean date/times) work on the web (by which I mean in JavaScript and, hence, in Bubble), where we know that dates are selected/constructed in the user’s timezone (by naïve datepickers), how do we ever make such a system work?

Well, to make that work, you first decide that all of your dates (proper date/times) represent dates in some fixed timezone. And that this timezone must not have DST adjustments, etc.

There is a timezone for just this purpose, and it’s called UTC (“Universal Coordinated Time” or “Coordinated Universal Time” - the abbreviation doesn’t match because of the French, who call this “Temps Universel Coordonné” and insisted on a compromise with the people who speak normal English. The nerve!)

Anyway, UTC is the time used on servers (and, not surprisingly, Bubble’s backend runs in UTC timezone – specifically the timezone known as “Etc/UTC”, but that’s the same thing as UTC or Zulu time – which is a more military name for UTC.)

So, to implement a “floating dates” approach, what we would do is construct all of our calendar dates as dates (proper date/times) that represent the starting moment of the calendar date in the UTC timezone.

And, when we wish to represent that date to the user, we format the date in UTC (as something like DD/MM/YYYY if you’re American or MM/DD/YYYY if you’re European). We can do this part (formatting the date in UTC) natively in Bubble.

Now comes the problem: How do we construct dates or pick dates in UTC?

Well, in vanilla Bubble, we cannot.

In my video, I explain how Calendar Grid Pro does this and there are other timezone-aware date pickers as well (like @gaurav has one that’s more like the Bubble default datepicker which will let you pick dates in a specific zone, like UTC).

We might also use whatever damn datepicker we want and then convert the picked date into what I call a “parallel date” in some other zone. This would require us to use a library to do so without tearing our remaining hair(s) out. I’ve shown how to do this in other posts using the moment library, but nobody knows what I’m going on about.

Perhaps I’ll build that. What would you pay for that?

cmarchan · 2021-01-26T13:44:20.279Z

This detailed explanation reminded me why I found your video so interesting.

Thanks Keith. You outdid yourself again!

I am bookmarking this post.

jasonturo · 2021-02-01T18:56:15.773Z

Hi @Keith,

Thank you so much for the thorough response on creating a common time in the db across time zones last week. To answer your question, if there was a plugin or elements available to purchase I would happily pay $100 (comparing to your Calendar Grid Pro plugin, or a feature to add to CGP?). I sent a direct message on this topic to you last week, not sure if it made its way to you?

What would you need from me to work on this?

All the best and thank you so much,
Jason

keith · 2021-02-02T03:13:18.446Z

This is actually something I’m working on now. ETA by end of this week, most likely (possibly sooner), but testing robust plugins is a bit arduous. (If you were just going to code something like this for yourself, it’s a couple hour project – or even much less – depending on how fancy you wanna get. But building a useful Bubble plugin for just anybody to use requires rather a lot more time. I know most Bubble plugins are kinda crap, but of course mine are not. )

jasonturo · 2021-02-02T21:39:41.205Z

I have no doubt of that. Keep up the great work, Keith!
Jason

jasonturo · 2021-02-19T17:50:10.544Z

Hi @keith , wondering how this project is coming along and if I could help support you in any way?

Happy Friday!
Jason

keith · 2021-03-10T07:57:54.119Z

Well, I kept adding and adding and adding features, but I finally submitted this to Bubble for review today. Within a day or so, you should be able to find my new plugin “Parallels: Date/Range Transformer” in your plugins tab.

A very brief video preview and the text description (also links to demo project that doesn’t do much ATM) over in the official support thread for this new plugin:

Parallels: Date and Date Range Transformer (Now with Video Demos!

) Showcase

Coming soon: Another cool-ass plugin from the person that brought you List Shifter and Calendar Grid Pro (that’s me). Here’s a preview: The app I’m showing in the intro: Runtime: https://list-shifter-demo.bubbleapps.io/version-test/parallels-demo?debug_mode=true Editor: List-shifter-demo | Bubble Editor The boring text version of what Parallels can do: Generate and transform dates and date ranges like a boss. Parallels is the awesome date and date range transformer that so…

Cheers,
Keith

system · 2021-04-03T23:37:03.200Z

This topic was automatically closed after 70 days. New replies are no longer allowed.