[SOLVED] why the GMT changes while storing DateTime type value?

the first attachment shows the date value that was passed to the DB. the second attachment shows the stored data in the DB. and the third data shows the data value that was returned by appwrite while listing

the passed value is "23:59:59". but stored and returned value is "17:59:59"

Okay. What I think happend is that the JavaScript convert it to your local timezone.

MariaDB remove the timezone so in timezone 0 this is indeed the time.

A way around would be to be sure you don't have timezone when you send the JavaScript

but i explicitly need timezones. cuz i'm working with different timezones than my local

Mmm Let me check something real quick

cool

Can you show me the code in which your creating the document and inserting the date

I have this storableData named big object. You can check the betDay prop's value in the first attachment. and the value is coming from another file that looks something like the second attachment, where I'm setting the time precisely to "23:59:59"

Try to to something like this

 return localDate.toISOString();

This way you'll return a full ISO date with time zone

Use this method on any date you're sending to database

i tried this(first attachment). also this (second attachment). but nothing is solved. just in case of return localDate.toISOString();, I got the milliseconds also added in the DB (third attachment), which wasn't getting added till now

Okay, So I went over the code to double check, and this is actually what happens and what your workaround should be.

What actually happens? Appwrite using MariaDB (MySQL) Datetime type to store dates. And this type (Actually all MariaDB Date type) Don't support timezone.

When you inserting a new date to the db the MariaDB will convert it to the server current timezone.

That mean you get your time value store right but with a different timezone.

Postgres for example have a special type to store datetime with time zone

What you should do. This is a very common workaround in the world of MariaDB & MySQL. you will need to convert the date each time you're getting it from the database. And Appwrtie always returns the date timestamp

Inserting When you inserting or updating new date into the database to it as you use to.

Getting When you're getting (selecting) date attribute make sure to convert them

Querying When running quires with dates make sure to use the server timezone. In your case it's UTC.

Hope this gave you enough to workaround

I've converted the date to utc and then queried the db with that date. the first attachment shows the iso-formatted date string, that I'm sending in the query. and the second attachment shows the date that my db have. as you can see, both are same, but it's still returning 0 documents (NOTE: i'm doing equal query)

Try to change the date in the query to be something like that.

const date = new Date().toISOString().slice(0, 19).replace('T', ' ');

Test it and lmn

tested. still getting zero docs

Can you share the code?

sure. here

And without the betDay it worked?

yes. I've finally successfully discovered that I'm dumb and I should rethink my career choice. i was passing a Boolean value as a String

thanks for your help btw

👍

[SOLVED] why the GMT changes while storing DateTime type value?