An "Easy" Way to Convert Bubble Date/Times to the Same Date, but in a Different Timezone

Remember that searches are done server-side.
So when performing a search, no matter where the search was initiated from the date being used is the date stored in the server.

It’s only when dates are “displayed” that the user’s timezone is used (which you can even control).

The best way do deal with date and time ambiguities is to always let the user know or specify exactly the timezone a date is in.

Date of birth is an interesting case because normally you don’t specify the time and timezone.
So lets say you were born in Toronto (GMT -5) on 3rd Dec, 1980 at 11:30pm.
This time in London (GMT + 0) will be 4th Dec 1980 4:30am.

So if you ask a user to enter their date of birth, because they were born in Toronto on 3rd Dec, no matter where they’re are currently (say in London), they’re going to enter it as 3rd Dec 1980.

So if you really need minute accuracy the only way to avoid this will have been to let the user specify their country of birth. Then you could use that information to know the timezone and store the date accurately.

1 Like

Right. You’d just store the date object, @mebeingken. You can set the time parts to 0 if you want before storage or as part of retrieval. If you really want to do this absolutely “correctly”, you would want to normalize birthdates to some arbitrary timezone (like UTC is often used for this purpose but in fact you could pick ANY zone).

Moment has a UTC mode for things like this.

And then, when a searching user is specifying start and end dates for which to find (for example) all users with birthdays between those two dates, you’d convert the start and end points of the search to the chosen arbitrary zone (in the way I describe in the original post).

Essentially the above is what I do in my vacation rental calendars. And it’s more-or-less what things of that nature do. For example, an iCal URL from HomeAway or VRBO lists start and end dates for a reservation where the start and end dates are assumed to be the first moment of the day in question. And there’s no timezone metadata attached.

Here’s an example of a typical iCal VEVENT like this:

BEGIN:VEVENT
UID:e6c722b0-a388-4018-93e6-04b0414e84be
DTSTAMP:20181203T231539Z
DTSTART;VALUE=DATE:20131219
DTEND;VALUE=DATE:20131222
SUMMARY:Reserved - mary & millard anderson
END:VEVENT

See how there’s no timezone info except in the datestamp field? (that’s when the file was generated)

So what do you do? Well, it depends on how and when/where you turn those text values into real date objects. If you’re doing it in the browser, the start and end dates are going to appear as though they are in the browser’s timezone.

If those texts representing dates are brought into Bubble via the api connector (and you tell bubble “hey, these are dates”), they’ll get turned into dates at start time 0 on that day in UTC.

What I actually do is I have an API that does the conversion before passing back the dates. And it passes them as texts (of course), but they are fully specified simplified extended ISO date texts and so they have all the timezone info in them. And my API actually takes an input for timezone – the dates that get passed back are transmuted to that target timezone.

This is a little bit fancier (but not much!) than just assuming UTC. (You still have the challenge of presenting the information in the user’s browser in such a way that it appears that the UTC dates actually start and end at the correct time when shown in a calendar.)

Long long long story short, if you have a datewise comparison and all of the dates under consideration are from the same timezone, you don’t have any of the weird issues that you’re anticipating.

But to @seanhoots more recent point: Birth dates are like the dates I showed you in that iCal feed. A user is born somewhere and likely does not currently reside where they were born, but of course just enters their birthday as the date upon which they were born, without consideration of timezone issues.

Basically, when we ask the question: “Out of a batch of people, which ones were born between date X and date Y?,” what we are doing is saying/asking, “Assuming that everyone’s birthday, date X and date Y are in the same timezone, which birthdays are in that range defined by X and Y?”

“Ignoring” the timezone aspect is assuming some sort of unified timezone (which we might call a “null” timezone) shared by all of the dates that we are considering. We are also at the same time ignoring time components and setting them to 0 in our heads.

You can pick whatever timezone you want to represent this shared or null timezone.

Hey all, I have a working custom plugin that solves this exact issue if anyone is interested. It’s currently in Private Beta only and is plug and play

It takes into account the User’s timezone (via browser) and converts the date into another date based on what timezone is chosen so that the date you choose is the date you get at the timezone

That may be useful to some folks. My take on this type of thing is that it would be most useful if available both server and client side. At the present time, one can’t install arbitrary NPM packages for Bubble server-side plugins, so Moment/Moment-range/Moment Timezone support there just yet.

On the browser/client side, I’m a big advocate for NOT relying on plugins in the vast majority of cases. Folks need to just learn the basics of installing useful packages, calling the parts of them that they find helpful via JavaScript (using Toolbox, which should really be a native part of Bubble if you ask me), etc.

The “you don’t have to be a coder” positioning aside, learning a few simple things like this greatly expands one’s capabilities.

I do wish that Bubble would implement moment and moment timezone inside of Bubble and just make some cool operators available in the native platform. They’re pretty essential functions and would make a nice complement to the already pretty powerful date/time features.

1 Like

@keith @seanhoots Thanks for all that – I’m no longer wrapped around the axle. :slight_smile:

What I heard was: For dates that people don’t associate with a time zone (like a birthdate,) save them as UTC, search for them in UTC, and format them UTC. In doing so, you can just use a single date field.

More testing to be had, but this seems fine:
Load the scripts in html element

Entering the birthdate:

Convert DOB to UTC before adding to database

Then for the search:
Convert search date to UTC

Reference the UTC search date in the search and set the time ranges

For testing, this was helpful:

Thanks again Gents!

3 Likes

Thanks @peng.o. I’ve got it working, but glad you jumped in with another option!

Yes, you understood what I was getting at! (sorry for the long note with lots of words) Again, if you wanted to compare vs. some other date, date range or whatnot in a datewise way, you can do a similar transform on the comparison date objects and remove any doubt about differences due to timezones.

Alternatively, you can use the unix value. The number of seconds passed since 1-1-1970

Add this in the API connector:

Then store a date, passed through this API, as a number in the DB

image

For display:

image

2 Likes

I am reading this topic because of the same problem that the rest of you have had and it seems ridiculously complicated. When a user enters the date of birth surely it could be as simple as a setting to “Change timezone to” and set the timezone to UTC. We already can change the hours, minutes etc. Am I missing something here?

@keith made a plugin…I haven’t looked yet, but maybe it will help you.

Hi @arnold.smyth, I built an explainer app with an easier to follow method as well. See my replies and links in this thread:

How To Schedule API Workflows Based On A Date In A Different Timezone Than Current Users Timezone

I also have a forthcoming suite of server side actions that also help with this sort of transformation (and lots else).

1 Like

I don’t think it will - what we need to do is take an input like: 13/02/2019 and save it as 13/02/2019 00:00 UTC. What is happening is that when I update the ‘thing’ in the DB it converts the date from whatever timezone I am in to the equivalent time in UTC and then saves it. There is no easy way around this behavior in Bubble because when you format that date first and then try to save it Bubble sees it as a text.

Yeah, my BTZ&L plug-in is not specifically helpful in doing this date transformation (though it can be in certain contexts). Anyway, @arnold.smyth, you are correct that to make a date object in a zone, we take static values for the year/month/day and then construct a date in the zone.

This can be done for example using moment-timezone – my preferred solution – OR we can do a bunch of math to find the correct offset from UTC and construct the date in Bubble by setting its time properties.

The former solution in the paragraph above is what the initial post here explains and a similar (and I think better) way of doing it is shown and documented (you gotta examine the edit mode) in the “Timezoner” explainer app.

Thanks @keith , I have looked at the moment solution and can implement it. My fear with this approach is the manual complexity - which I can do as the developer but the client may not be able to maintain if something goes wrong. It may be the only solution for now but a “Change to UTC” option in the menu would be nice.

1 Like

This is why I will eventually (soon-ish?) release a paid server-side plugin for this. That’s really where this should live, but it needs to be performant enough and we seem to not quite be there in terms of how these work in Bubble right now. (I won’t belabor this point further here.)

Or a feature add that allows us to set/store/retrieve date values without any timezone adjustment…just a good 'ol fashion date field.

@mebeingken we have this already. You’re talking about converting a date/time object to a static, non-date representation, such as a string. The date:formatted as… operator is that.

We can do that all day long, and with a great deal of flexibility.

However: There is no such thing as a date/time object without a GMT offset. A date/time (“date data type in Bubble” – which is the same thing as a "date data type in JavaScript) describes a unique point in time.

What Bubble does not give us an easy way to do is to take a text representation of a date (which, again, we can make with a great deal of flexibility) and, from that representation, create a new date object *with the correct offset from GMT such that the new date object could be said to be “created in some arbitrary timezone”.

And, of course, JavaScript does not give us such a feature either. Hence, packages like moment-timezone, which give us a way to do this with a bit of ease.

(If you’re interested in this, you might want to check out my Timezoner explainer app which might give you an a-ha moment.)

Anyway, the way that moment-timezone is used to create a date in a given zone is to start with an unambiguous text representation of a date/time, create a moment from that, and then turn it back into a JS date object. (If the source thing is an already existing moment, we actually do the same thing.)

1 Like

I just want a field type, that behaves like a traditional database. Set a value, and when you lookup that value, it will be exactly as when you put it in… Regardless of your location.

1 Like

But it is exactly what one puts in. For example:

  • A user uses a date/time picker to pick a date.
  • This date represents a unique moment in time (as all dates do)
  • You save this in the database as type date (as you should).

Later, you retrieve this value. The value you retrieve is the exact same unique moment in time that the user specified.

That it is confusing that this object may look different to you when displayed in the browser is beside the point. The object you are looking at is the same object that was put in the database and describes the exact same moment in time. See?

BUT: What we often desire to do is what @arnold.smyth here desires to do:

The user selects some date/time. But we do not want the moment in time that the user has selected. What we want is a different moment in time… one that shares some characteristics of the date the user picked, but is not in fact the exact moment the user indicated to us.

(We imagine, as @arnold.smyth does, that we want to take that selected date – which might be something like February 13, 2019 at Noon in America/Los_Angeles – and “turn it into” February 13, 2019 at Noon in UTC. These are different moments in time and, hence, different date objects.

I have dubbed these things “parallel dates”. There’s probably some other name for them, but I like mine as I think it sounds quite intriguing!)

The issue with date pickers and the client is this: JavaScript (and, hence, all web browsers) will only construct a date in the client’s timezone. (This goes for servers running nodejs as well – in vanilla node, constructing a new date will make that date in the server’s timezone (which is usually UTC but does not have to be).

So, you have to be careful with what you’re snagging from a datepicker – or indeed from a date created server-side – you need to remember what it represents. (We wish that JS had not made the silly decision it did to treat dates this way – it might have been better if JS dates were always created in UTC, rather than having a UTC offset, but there’s nothing we can do about that.)

JavaScript dates are just difficult like that. (Hence, alternative solutions like moment.js. Moment objects are similar to JS dates, but are a bit more flexible, especially in the “moment-timezone” version.)

In vanilla Bubble, there is little we can do to work around this without totally adjusting your mindset and storing dates as string representations – but this is a lotta lotta work and you lose the ability to easily do things date-wise.

So: It would be nice if Bubble had a built-in function or operator to do this parallel date creation thing (which I why I’ve built a variety of things like this as plugins… those things are not available yet as server-side action plugins are a bit problematic right now in terms of speed).

1 Like