How Can I Store and Retrieve UTC Based Dates With the Z Character?

Wappler General Need Help
1

Hello there @Teodor…

I want my users to capture a datetime via the date picker, and to store that value via PHP in a MySQL database as a UTC time. I then want to be able to retrieve the time and display it in the user’s local timezone.

It seems that working with a datetime in the ‘Z’ format (e.g. 2023-08-29T08:00:00.000Z) is the way to do this as Wappler will automatically convert this format to the user’s local time when displaying it.

I’m trying to find the best workflow to do with with PHP/MySQL, but am having a few problem with retrieving the datetime again.

Here is the process I am following:

1. Create the datetime with the datepicker id=“i_datetime”
This creates a datetime like this for 09:00 on the 29th August:

i_datetime.value = 2023-08-29 09:00:00.000

2. Convert it to UTC time and the Z format on the client side
This correctly converts the time to UTC (08:00, -1 hour) and creates the Z formatting:

i_datetime.value.toUTCDate() = 2023-08-29T08:00:00.000Z

3. Pass this to a Server Action to Store in a MySQL datetime field
This kinda works okay… except when saved to MySQL, the T and Z characters are stripped out, so what is saved in the database looks like this:

2023-08-29 08:00:00

4. Retrieve The Datetime From MySQL To Display to User
So of course, when I retrieve the value stored in MySQL, it is the correct UTC time (08:00), but is not in the Z format.

So here is my question…

How can I most easily retrieve the date from MySQL via PHP to re-format the date with the T and Z but make sure the time itself if not changed?

Or…

Is there a better way to do this that I am missing?

Many thanks!
Antony.

2

Oh, and one more thing… I’d love my solution to this to work correctly with node/MySQL as well as PHP/MySQL!

3

@Teodor, do you have any ideas about this?

4

With NodeJS you can set Timezone Handling on the database connection, there you can tell the database driver how time should be returned (local or UTC). In PHP this is currently missing.

Btw. the datepicker has an option to use UTC, so the formatter is then not needed since it will have the UTC directly as value.

5

Thanks @patrick… so to retrieve the date time with a Z from MySQL it looks like I need to add it manually via a MySQL view.

Do I need to add the T too? It seems to do the time zone translation with a space and Z as well as with a T and Z in Wappler 3.9.7…

6

Don’t know if it is easy to do in a MySQL query/view, alternative you could use a formatter in a repeat of the database result and return that as action output.

7

It is dead easy to do in a view… most of my queries are complex views so I can add it in.

Do you think I need the T too, or will a space be okay?

8

It depends on the browser datetime parser, the ISO 8601 standard doesn’t allow a space, but RFC 3339 allows it.

I’ve tested on Chrome, there it works with a space.

1 Like