Modifying dates within a trigger

Hello - I have a simple trigger that will manipulate and update a date column based on a value in another column.

Basically when a row is modified in the table the trigger will look at the “now” value of date1 and update date2 but with the date but a time of 00:00:00 using the following
image
The problem is that the dates are being updated to random values like 5 hours from now etc. They never update to the expected value.

If I run the same logic through a workflow instead of a trigger the date updates to 00:00:00 as expected

Should this not be done in a trigger? or is the trigger not working correctly.

Try referencing “schedule now’s date” instead of “this schedule’s date.”

Tried that, same thing.

This is very important: The result is realy random or it is always +5??

Original Modified from trigger
3/10/22 12:00 PM 3/9/22 6:00 PM
3/10/22 11:00 AM 3/9/22 6:00 PM
3/10/22 10:00 AM 3/9/22 6:00 PM
3/10/22 9:00 AM 3/9/22 6:00 PM
3/10/22 8:00 AM 3/9/22 6:00 PM
3/10/22 7:00 AM 3/9/22 6:00 PM
3/10/22 6:00 AM 3/9/22 6:00 PM
3/10/22 5:00 AM 3/9/22 6:00 PM
3/10/22 4:00 AM 3/9/22 6:00 PM
3/10/22 3:00 AM 3/9/22 6:00 PM
3/10/22 2:00 AM 3/9/22 6:00 PM
3/10/22 1:00 AM 3/9/22 6:00 PM
3/10/22 12:00 AM 3/9/22 6:00 PM
3/10/22 11:00 PM 3/10/22 6:00 PM
3/10/22 10:00 PM 3/10/22 6:00 PM
3/10/22 9:00 PM 3/10/22 6:00 PM
3/10/22 8:00 PM 3/10/22 6:00 PM
3/10/22 7:00 PM 3/10/22 6:00 PM
3/10/22 6:00 PM 3/10/22 6:00 PM
3/10/22 5:00 PM 3/9/22 6:00 PM
3/10/22 4:00 PM 3/9/22 6:00 PM
3/10/22 3:00 PM 3/9/22 6:00 PM
3/10/22 2:00 PM 3/9/22 6:00 PM
3/10/22 1:00 PM 3/9/22 6:00 PM

Results from each hour over 24 hour period above. I wouldn’t say its random but its not consistent either.

It looks like you are having timezone problems.

When you manipulate dates/hour in backend workflows, Bubbe will use Bubble’s server timezone and will convert the time to it’s own timezone, this may cause this type of issue.

When you manipulate dates in regular workflows, Bubble will use your timezone. That is why when you run the workflow from your browser, you get the desired result.

Where are you from? And what is your timezone?

(Edit)

Analyzing the data you provided, it seems that the difference between your timezone and Bubble’s timezone if 18 hours.

Thats possible although it would make more sense if I was telling to to add or subtract time from CurrentTime which would be on the server and not here.
In this case I’m taking a static date value and setting the hours/minutes/seconds to an arbitrary value so none of those should be dependent on time zones.

I’m in Canada in CST.

Lets analyze some examples you gave:

1) 3/10/22 12:00 PM → 3/9/22 6:00 PM

When Bubble read 3/10/22 12:00 PM in its server, it will aply -18 hours imediatly to convert the zone, that will make your date became 3/9/22 6:00 PM.
Now Bubble will apply the 00:00:00.
Your date will now became 3/9/22 12:00 AM
When you see it from your site (where the difference is +18 hours) you will see 3/9/22 6:00 PM

2) 3/10/22 7:00 AM → 3/9/22 6:00 PM

When Bubble read 3/10/22 7:00 AM in its server, it will aply -18 hours imediatly to convert the zone, that will make your date became 3/9/22 1:00 PM.
Now Bubble will apply the 00:00:00.
Your date will now became 3/9/22 12:00 AM
When you see it from your site (where the difference is +18 hours) you will see 3/9/22 6:00 PM

3) 3/10/22 8:00 PM → 3/10/22 6:00 PM

When Bubble read 3/10/22 8:00 PM in its server, it will aply -18 hours imediatly to convert the zone, that will make your date became 3/10/22 4:00 AM.
Now Bubble will apply the 00:00:00.
Your date will now became 3/10/22 12:00 AM
When you see it from your site (where the difference is +18 hours) you will see 3/10/22 6:00 PM

That is what is happening.
You need to compensate this difference during your workflow to prevent such behavior.

It makes sense and I could adjust the logic, but what if the time zone on the server changes? That’s something I can’t predict or control.

Also - for a test I told the trigger to insert currentdatetime instead of manipulating the dates and it updated it correctly to my local timezone.
Shouldn’t that also be 18 hours off?

And here’s some more random test results which don’t seem to follow the pattern

Original Modified from trigger
5/10/22 8:00 PM 5/10/22 7:00 PM
5/10/19 8:00 PM 5/10/19 7:00 PM
9/29/19 6:30 AM 9/28/19 7:00 PM
1/1/20 9:30 AM 12/31/19 6:00 PM

You are just adding +18 hours to the submitted date?

No its the trigger doing the work. I’m not adding anything to the dates.
And if I just tell it to update with current datetime it correctly uses my local time, not +18 hours

image

Can you make a test?

Try to use:

This Schedule’s date +18 hours :rounded down to date

This is very strange. I am in Brazil and I ALWAYS have to compensate my timezone ( UTC/GMT -3 hours) when I need to manipulate date/time in backend.

Ok - results below

Original Modified from trigger Offset
3/10/22 6:00 AM 3/10/22 6:00 PM +18
3/10/22 6:00 AM 3/10/22 6:00 PM +24
3/10/22 6:00 AM 3/10/22 6:00 PM +12
3/10/22 6:00 AM 3/8/22 6:00 PM -18 just to see if the offset worked

image

Ok! Now…. Final test

This Schedule’s date +18 hours :rounded down to date -18 hours

I think that using the -18 hours in the end, you will finish with the desired result (00:00:00)

Tried it - it works sometimes but not all the time.
May 10, 2019 8:00 pm
turns into
May 10, 2019 1:00 am

but

Mar 10, 2022 9:30 am
turns into
Mar 10, 2022 12:00 am

image

Hi there,
Were you able to solve this issue?
I have a database trigger backend workflow that updates fields based on database dates and it seems that sometimes the backend uses some server date and sometimes my timezone (-2 hours). Then, the result is not the same. I checked by sending an email when the workflow is triggered and sometimes I have a server time displayed and sometimes my timezone. I seem to have my timezone when I use autobinding to trigger the workflow. Anyone has an idea?
Thanks