1

I'm writing a query (MySQL) to retrieve particular data set by passing the 'date'. My database columns are set to local (Sri Lankan) date/time and the time I get from feeds are in UK time. My issue here is, if I passed the date '2015-04-30' and get the results, it will not show the result after 00:00 in local SL Time, but records are available as they are in UK time and should return those results.

What is the best approach to query or to sort out this date/time issue?

Nomesh DeSilva
  • 1,649
  • 4
  • 25
  • 43
  • please show your code – MAC Apr 30 '15 at 08:59
  • its a huge query. date check is "AND m.date = '2015-04-30'" – Nomesh DeSilva Apr 30 '15 at 09:04
  • you're not using parameters? how about this code "AND m.date = "# & dateVariable & "#" – MAC Apr 30 '15 at 09:13
  • and try to check whether your server time is really set to what you said Sri Lankan time... maybe it was configured ti UK time when you first set your PC – MAC Apr 30 '15 at 09:14
  • 1
    try to read this if this has quite same issue as you do http://stackoverflow.com/questions/6900629/using-parameters-in-an-sql-statement-which-contains-date-format – MAC Apr 30 '15 at 09:19
  • 1
    See datatypes `TIMESTAMP` versus `DATETIME`. If you aren't given a time with the date, '2015-04-30' in the UK might match either '2015-04-30' or '2015-05-01' in Sri Lanka. – Rick James May 02 '15 at 18:00
  • Check this if it helps: http://stackoverflow.com/questions/15017799/how-to-convert-utc-date-to-local-time-zone-in-mysql-select-query – Swati Joshi May 05 '15 at 11:40
  • 1
    you should always convert both dates to same time before doing comparison, it may be your local time or UK or GMT, but depending on time of day and different in time between countries you will have different records that are in two different days. –  May 19 '15 at 20:11

0 Answers0