Date Range Picker Front End Time UTC

[scott]

I have a form on the front end which has a date range picker. A user selects their date and time with no issues. The data is inserted into the database with no issues.

The issue is that the time being selected on the front end is being treated as UTC time versus the local users time.

So, when a user selects 10 AM, the time being inserted is 10 AM, but when it is retrieved, it is converted to the local user time. For the Central Time zone, it is currently -5 UTC, so it shows 5 AM.

I have not been able to see any setting in the Date Range Picker that would give the option for the time to be considered local user time, so that means I need to look at a server action solution. (I think).

The issue is I am not sure how to convert the time on the server side. I attempted to use "toUTCTime()" formatter on the Database Updater but it throws an ER_TRUNCATED_WRONG_VALUE error, I presume because the database field is datetime and it is trying to insert "2024-07-01T10:00:00.000Z" versus "2024-07-01 10:00:00".

[famousmag]

Hey Scott,

I suppose you compare the date entered on the page with the date saved on db?

Have you tried showing the saved date on your page with a formatDate to see this date?

Anyway, how you want to save the dates? serverTime, UTC, local?
No matter how you save it in your db, you can always translate/show the local time on the front-side (as long as you know how it is saved in your db)

[scott]

The Server Connect Settings is set to Etc/UTC. The format that the datetime is saved in the database is UTC because users may be from any of the U.S. time zones and we need a consistent way of handling dates/times.

When the user selects a date/time using the Date Range Picker, they are using the date/time of what time they are located at.

What is inserted into the database is the exact date and time that they selected.

When the server connect retrieves the data, it shows it under network as the time they selected.

When displaying the date/time on the page, it shows the local time.

There is no date/time conversion coded on the front end (only formatting) so I assume it is being done automatically.

So what I need to know how to do is using the Date Range Picker, insert the date/time selected into the database in UTC time since it is being converted to local time on the front end.

[franse]

@scott

When displaying the date/time on the page, it shows the local time.

Are you using node?

@scott

There is no date/time conversion coded on the front end (only formatting) so I assume it is being done automatically.

[scott]

Hey there @franse, yes, I am using Nodejs and the Timezone in the Server Connect Settings is set to Etc/UTC.

[franse]

@scott

When displaying the date/time on the page, it shows the local time.

What about serverconnect3.data.query[0].date.toDate() ?
I made a test, in my case:

1. serverconnect3.data.query[0].date
   
2. serverconnect3.data.query[0].date.toDate()
   
3. serverconnect3.data.query[0].date.toDate().formatDate('dd/MM/yyyy HH:mm')
   

This has been discussed a few times, honestly, don't remember exactly what's the issue about this.
But remember that patrick has explain it

[scott]

The issue really is not about the retrieval of the data, but the insertion of the data.

I have no issue with the data automatically being converted to local time.

However, trying to explain to users to submit the data in UTC time is difficult and causing complaints and issues. We tried it this way, but users do not like to have to think and do math.

The way I am looking at it, if it automatically converts UTC to local time on retrieval, there must be a way to convert local to UTC on insertion. I wonder if @patrick has any thoughts on this.

[franse]

@scott

trying to explain to users to submit the data in UTC time is difficult and causing complaints and issues

If you need to submit the data in UTC time, maybe you can use two date and time component, one local, the other utc:

Make a diff on the server side between both date and time component and then addhours

Or maybe submit local time with date and time component and compare it with NOW_UTC and add/substract difference?

Just thinking loud here..

[scott]

I could try that and see if it works with the Date Range Picker. Not a bad suggestion.

[Teodor]

Please check the following article, it explains how to configure date and time handling for nodejs:

Date and Time Settings for NodeJS

Intro In this tutorial we will explain the different date and time settings available for NodeJS and how to handle time zone options. Server Time Zone You can set the server time zone settings depending on your needs. The settings depend on if your are using Docker or not. Using Docker When you are using Docker, then you can adjust the server time zone settings in the target settings: [Screenshot_2] You can select UTC or your specific time zone from the list: [Screenshot_3] Not Using D…

[scott]

Unfortunately, @Teodor, the following applies to Date Picker, but not Date Range Picker.

@Teodor

Using Date Picker

If you are using the Date Picker component, you can send the date as selected or send it as UTC. If you don’t send the date as UTC then it will be handled as if the client inserting is was located in the same time zone as your server.

If the UTC option is enabled, it will be sent to the server as UTC and properly converted to the server time zone before storing it:

Date Picker Option:

Date Range Picker Option:

[franse]

@scott

not Date Range Picker.

You can add utc="true" to the code:

<input id="date2" name="date" is="dmx-date-range-picker" timepicker="" utc="true" use24hours="true" utc="true" type="text">

+3 in my case

[scott]

I get my favorite error:

"ER_TRUNCATED_WRONG_VALUE"
Incorrect datetime value: '2024-07-04T15:00:00.000Z' for column 'dates_start' at row 2

Here is the code:

<input id="inp_dates" name="dates" is="dmx-date-range-picker" timepicker="" utc="true" minutes-increment="30" type="text" class="form-control" dmx-bind:startdate="sc_read_regional.data.read_regional.dates_start" dmx-bind:enddate="sc_read_regional.data.read_regional.dates_end">

[franse]

Your insert is like this?:

[scott]

No, my type is datetime not date and I have a condition set.

[Cheese]

@scott

Incorrect datetime value: '2024-07-04T15:00:00.000Z'

You need to format the date time as the time code will not be accepted by the database:

{{$_POST.dates_start.formatDate('yyyy-MM-dd HH:mm:ss')}}

{{$_POST.dates_end.formatDate('yyyy-MM-dd HH:mm:ss')}}

[scott]

Perfect! Thank you @franse and @Cheese !

[Cheese]

You'll more than likely have to apply the same formatting on any updates also @scott

[scott]

True enough, doing that now. Now i just gotta remember this in the future.

[franse]

Now we are here, does invalid date with time works?
I mean, restrict this range doesn't seem to be working: