DAYOFMONTH 1 day forward?

I have a generated date column, this is the logic -

You can see how the resulting column is 1 day in the future -

image

I’m guessing this has something to do with time zones?

Thanks,
Tim

Hi Tim,

Yes, it is related to the timezone. Here’s an experiment I just did. I added a generated column that shows day of the month for the values in the created column.

When Dates/times are shown in console in UTC everything looks correct:

When Dates/times are shown in my current timezone, the dates in the created column are converted to CST and appear off:

The database stores all dates/times in UTC, so the calculation of the day of the month is correct.

Regards,
Mark

Hi @mark-piller,

I figured the calculation was correct. Thanks for explaining the issue to me.

My StartDate is imported from an external system that stores the DateTime in local DateTime. I’m guessing when it is inserted, Backendless treats it as UTC, which is skewing everything.

Would you suggest offsetting the DateTime on insert to account for UTC? Or another suggestion?

Yes, offsetting the timestamps on your date objects is the way to go. If you set it to 23:59:00 before it is saved in the DB, it should work uniformly across the globe.

Thanks, @mark-piller!

@mark-piller

Would you be able to point me in the right direction on how to always see the time at 23:59:00? My first thought is to take the external date string, use the convert string to date block, break out the month, day, and year into a new string, add 23:59:00 to that string as the time and then use the convert to date from string block again to make a new date object.

But that seems kind of insane, so I’m hoping you have a better suggestion.

Tim

In which time zone? Your local or the UTC?

From your previous post, I figured that would be UTC, so any time zone would always be on the same day.

I’d try the following algorithm:

  1. Get the value from your DATETIME column
  2. Get the time portion of the timestamp
  3. Calculate the difference between 23:59:00 and the actual time from (2)
  4. Add the difference (3) to the time (2)
  5. Write the object back to the database.

Regards,
Mark

Thanks!