0

I have a scheduler program I am writing for a school project. The MySQL database is in UTC, the scheduler interface is in local time. I have everything working just fine, converting back and forth like it should, except that I am in PST, which is -8 hours.

Appointments that are set for after 4pm local time don't show up in the interface for that day, they show up on the next day. They show the correct day and time, but since I pull them from the database by comparing the database date, it believes they are the next day. (The interface in this case is a Visual Basic datagridview.)

How can I add those hours to the date before I compare it? Here is my query string:

STRING sqlString = "SELECT ADDTIME(START, '" + Global.OffsetString + "'),
  ADDTIME(END, '" + Global.OffsetString + "') 
FROM appointment 
WHERE DATE(START) = '" + DateOnly + "' AND userId = " + Global.uNum;

I know using the offset number may not be the best, but it is a global variable that is run every time they start the program, and this is for a class project. I only have access to the user side, I can't affect the database at all, so I can't use the tz.

The biggest issue is that I don't know how to add (or subtract, I am at -8 hours) the hours, when I am only comparing dates. If I try to compare full DateTimes then nothing shows up because it is trying to compare minutes as well.

Any help would be greatly appreciated.

ProgrammingLlama
  • 36,677
  • 7
  • 67
  • 86
TBaildon
  • 33
  • 1
  • 5
  • You can't just use -8. If you are in Pacific Daylight Time, it is a -7. See if this helps https://stackoverflow.com/questions/15017799/how-to-convert-utc-date-to-local-time-zone-in-mysql-select-query – JAZ Feb 07 '20 at 01:12
  • The real question is: why are you doing such gymnastics in SQL, rather than just pulling the `DateTime` out and calling `.ToLocalTime()`? – ProgrammingLlama Feb 07 '20 at 01:20
  • I am doing the gymnastics in MySQL because then it feeds directly into the datagridview. And I am doing the comparison in MySQL because I only want to pull down the records that match the day the person has chosen on the calendar. That is the DateOnly part it is trying to match to . The problem is that the DateOnly is local, and the MySQL date is in UTC, so sometimes the dates don't match. – TBaildon Feb 07 '20 at 01:49
  • This is one of the reasons why you should be using parameterized queries, and not passing strings in. The other major reason is that parameterized queries will avoid SQL injection. – ProgrammingLlama Feb 07 '20 at 01:54
  • They haven't really gone over those in class. This is supposed to be a C# class, but I keep getting hung up on the MySQL stuff that they didn't really teach us much about. I have seen a lot of answers talking about them, but hadn't thought about them for this issue. If that is the only way to do this I may need to do it that way. – TBaildon Feb 07 '20 at 02:03
  • Have you tried `set time_zone` in mysql? It changes your connection, but doesn't modify the database. – yaakov Feb 07 '20 at 02:10

0 Answers0