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

@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

Let me try it this way…in mysql there are “datetime” types (date time without zone info) and “timestamp” types (moment in time)…I would benefit from having datetime types in Bubble to go along with the timestamp types we already have. And while we’re at it, a “date” type would also be great so we can have dates without time.

I know how to deal with it in Bubble’s current environment, I just think there are more options available to make things easier.

Kudos @keith

@Keith2 first i gotta say, your content on this subject is excellent.

A question I have is:

If i store all dates in UTC, then i need to add travel time (say 9.4 hrs) to the UTC to calculate an arrival time, can i still format it for the timezone of the arrival destination so that it will read correctly for the user who is arriving in that locale?

Right now it looks like i have to go through all of my dates and change them to UTC, then run calculations on each UTC date to add travel time, then format in the time zone of the destination as text.

@JustinC I keep stumbling across your date/time posts!

UTC is time devoid of any local timezone offsets that make it look pretty to the user. If you add 10 minutes to UTC for travel, you are changing the time by 10 minutes. There is no separate time for your local timezone, just an offset number, which then applies x number of hours to the UTC time you modified.

  1. UTC 10:00:00 +10 minutes =10:10:00:
  2. When displayed with an EST offset 06:00:00 becomes 06:10:00:

#1 and #2 are literally the same time. Time on the web is actually a single number that is in UTC everything else is just making it appear correctly. If you live in the eastern time zone, when you see 6:00PM local time on your browser, there is actually an underlying UTC time of 10:00PM that when combined with your browser timezone offset number makes 6:00PM. You are seeing UTC time with a local timezone offset applied. If you add 10 minutes to 6:00PM to become 06:10 in bubble you are necessarily adding 10 minutes to the underlying UTC time number to make it 10:10 and then your browser is applying the offset to make it 06:100:

This is a little simplified in fact. Time on the web really isn’t stored as minutes at all but rather the number of milliseconds since Jan 1 1970 (also known as UNIX time). All of these +minute +hours +day operations you see are adding a number of MS to this number.

UNIX time (in ms) --formatting–> UTC (date/time) --add offset hours–> local time (date/time)

3 Likes

Agreed!

Seems like a great Bubblicous way to go about it! In fact, I referenced this post in a feature request I just submitted to the official Ideaboard.

To anyone who comes upon this post, please head to the Ideaboard and search for “Ability to save Bubble date data”. Then upvote it!

:slightly_smiling_face:

1 Like