Search code examples
mysqldatagrip

MySQL str_to_date returning the date minus one day in Datagrip

I appologize if I'm missing something, I've never encountered this, though.

I noticed my query was returning the wrong date, I was passing '2019-07-19' and it was giving me the result set for '2019-07-18'

Now I see this output: 

select str_to_date('2019-07-19', '%Y-%m-%d');

1| '2019-07-18'

Can anyone explain why that's happening? This isn't my database so I haven't configured any settings.

I'm using datagrip to execute the above query which gives the strange answer.

When I execute through the cli I get the correct return.

running 

SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone, TIMEDIFF(NOW(), UTC_TIMESTAMP);

1| SYSTEM    | SYSTEM    | UTC   | 00:00:00

in both environments shows the same thing

I also see that explitly stating the timestamp seems to return the correct response as well

select str_to_date('2019-07-19 0:00:00', '%Y-%m-%d %H:%i:%s');

1| '2019-07-19 00:00:00'

So it seems that datagrip is doing something wrong...

Solution

  • Set the corresponding time zone in the connection properties. enter image description here