Comparing Dates in Conditions, and Accounting for Timezones

I’ve just spent some time reading through several topics about how Bubble handles dates, and I’m still not sure how it will apply in my situation. Hoping someone can bring some clarity.

What I am trying to do is track some user interactions into a Stat Type. A simple example would be counting the number of clicks on a button. I will want an admin user to be able to view and export these as CSV. Here’s what I am thinking the workflow will look like.

  1. Create a row in the database for this specific button for this day if it does not exist.
  2. Increment click count.

The first step is necessary in my case because new info is added to the app every day and there is no reason to create stats for old items that are no longer getting clicks.

Now, here is where I am confused. The Bubble documentation says that dates are saved for the users timezone which makes sense if I want to display that date back to that user, but I need my stats to be locked to the Company HQ timezone, because they stats will lose meaning without a standard.

My questions.

Q1. Assume I create a new stat thing based on a user interaction and I fill a day field with date (hours, minutes, seconds set to 0) only when a thing with that day doesn’t exist; Will I get consistent results even if the first interaction is in New York one day and California the next.
Q2. If I have 2 admin users based in 2 timezones, will they see the same stats?
Q3. If not how do I standardize the day date field to an arbitrary time zone?

Ian

The other thought I had is that I could extract from the date to create an id for a day and test against that, but I am still unclear about how that is registered, and it’s hard to test this behavior since I only live in one timezone.

I hope it’s not too impolite to bump on this forum… because I’m going to bump this : )

Ok, I wouldn’t normally press, but I really don’t know how to test this on my own. How do y’all test for timezones? Do you spoof a timezone locally?

Does someone from Bubble have some insight? @emmanuel @neerja

Thanks for indulging me.

@subtlemonsters Bubble uses UTC time for calculations and dates are displayed in local time to users. You can spoof timezones by changing it in your OS as Chrome gets it from there.

2 Likes

Ok great. One more clarification. If I test a date for a condition of a workflow, is that date subject to timezone. I guess I’m not clear on whether that is considered server side or client side.

Example: Let’s say I want to log all user interaction with an interface object, so I have a workflow to create a thing that I only want to run once each day that I can then iterate a counter to. I could have a condition that says “only when current date (set hours, minutes, seconds all to zero) is not equal to a day field as date (set hours, minutes, seconds all to zero)”.

Does that work so that I have successive, non-overlapping day-by-day entries no matter what timezone triggers that workflow because the calculation is done based on UTC, or could that thing end up having an entry whose day field value is set to midnight in one timezone and then the next day entry could be midnight in another timezone?

If I’m just asking a stupid question here, please let me know.

Thanks, that’ll help

So I did some more digging and found this thread:

And I am starting to get why this is tricky. I think I may be able to phrase my question in a more helpful way now.

If for a workflow, I want to compare Current Date to a date stored in the database to see if they are the same day, are those both UTC because I am working with the database, or are they localized because they involve a user interaction? Or neither?

I bet @sridharan.s or @keith could set me strait :slight_smile:

I’m not 100% sure. I believe the way Bubble does this is that it converts all times to UTC. So 3pm Eastern is actually stored as 7pm UTC. So, in your case, it’d look at the dates to see whether the UTC version of the times has the same day.

Thanks for the reply. I’m going to dig in and start to test this.

Can anyone else confirm this?

I’ve confirmed that this does not work for me. It looks as if the created date is in UTC, but I can not leverage that, because I can’t modify the date in a constraint (i.e. change seconds to 0). If I create another field to store a day, it stores according to local time and so does not serve my purpose.

Is no one else trying to capture user stats like this? I wasn’t expecting this much resistance. I’m starting to understand the technical reason for the complication, but the use case doesn’t seem far fetched to me.

I guess it’s time to try to figure out a javascript solution. Is that right @keith?

What if I could leverage the difference between a date field and the default created date field?

What calculation are you trying to create? …I don’t understand, specifically, what the challenge you’re running into is

1 Like

I want to track User interactions on a daily basis. Specifically at the moment, number of plays per song.

Based on the ratio of my posts her to everyone else’s I assume I am either not communicating well or just missing something simple. But, here is the line of thought I’ve had on the subject.

  1. I thought I could just create a new thing for every play, but Users * Songs * Plays will quickly add up to a very large number.
  2. I could group these plays by time interval, so that I have a thing with 3 fields, Song, Timeframe, and # Plays.
  3. Based on some rough calculations, I think hourly things will will create too many, so I settled on daily stats.
  4. I don’t want to create a Stat thing for every song for every day because this is overkill.
  5. So I thought I could just create the thing the first time that song is played each day, then after it’s created, just iterate the count for subsequent plays.
  6. However, for these stats to be useful, that have to reflect the start of the day in a universal arbitrarily assigned timezone (preferable mine), not the timezone of whoever happens to be the first to play a particular song.
  7. I can zero out the hours, minutes, and seconds to set the day field to the start of the day, but there will still be a discrepancy based on what timezone that workflow is triggered from.

Hopefully that outlines how I got here.

I can be wrong, but the way I see it there’s 3 timezone here. You already know two. The third one is local server (Bubble AWS Oregon server). When you compute server-side (it take the local Pacific time). You have to take that in consideration. When I tested it, it was my conclusion. I got 3 differents timezone values (UTC, Bubble server and local browser timezone).

So what I’m seeing now is this. The Created Date field on any thing is stored as UTC, but if I run a workflow on the Created Date Field, say to create another field of just the day, it converts to my browsers time before saving that new field.

I’m going to get dinner and come back to this : )

A Created Date field is automatically added to every thing. If I also save the date in another field and look at them both in Bubble’s data tab, I see 2 different dates (my time and UTC.)

Does that mean they are stored differently in the database or is it just that Bubble has a display filter on one and not the other?

Try referencing them on a page and find out.

When you save something from your browser it converts it to UTC time to save it. So, that’s what’s going on here. If you want it a few hours different, then do current time :plus hours 3 (or something like that)