Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Performing Time Calculations for Time Zones

Posted on 2008-06-17
7
191 Views
Last Modified: 2010-03-19
I am having a problem with performing time calculations. My Server runs on ET (Eastern Time) However we have a few customers that are not in that time zone. All of the times that go into the database are all in ET. What I am trying to do is pull data and do a time calculation in the query to collect the correct records.

In the code included the startT and endT columns are time type fields.

The thinking behind the query is that if I add 3 hours to the current server time it will pull the correct records for people in california. i.e. if its 1 in california the server thinks its actually 4 so I need to pull records and add 3 hours to what the database has recorded so that I can compare it to the current time on the server.

I hope this makes sense.
SELECT users.firstname, 
	users.lastname, 
	day_planner.UID,
	date_format(day_planner.endT, '%h:%i %p') as ending
FROM 
users 
LEFT OUTER JOIN day_planner ON users.UID = day_planner.UID
WHERE 
	startD = CURDATE() 
	and 
	CURTIME() BETWEEN TIME(DATE_ADD(day_planner.startT, INTERVAL + 3 HOUR)) AND TIME(DATE_ADD(day_planner.endT, INTERVAL + 3 HOUR))

Open in new window

0
Comment
Question by:Tluckett
  • 4
  • 3
7 Comments
 
LVL 20

Expert Comment

by:virmaior
ID: 21806386
This might not achieve what you are hoping for, but one option would be setting the timezone for your west coast people.

http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html

SET timezone = 'US/Western';

at the beginning of each connection from them.
0
 

Author Comment

by:Tluckett
ID: 21806954
Unfortunately that option does not work and is something that was tried. Any other ideas?
0
 
LVL 20

Expert Comment

by:virmaior
ID: 21807014
I guess I would need to better understand what is currently happening here.

(1) Is this script being called from a webpage or a cron job?
(2) How are the users who are in california marked in the DB?

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Tluckett
ID: 21807190
Currently the Query is not returning results based on the query included here.

All the times are in the day_planner table and are stored in ET time. The users time zone is specified in an include file and the value of that variable is them placed into the query using a +/- # to indicate if their timezone is plus hours or minus hours of ET. This is something that is set up manually when we set up an account.
0
 
LVL 20

Expert Comment

by:virmaior
ID: 21807368
I am not sure why your query isn't working without looking at your data.  but why bother storing the values offset from the actual time?
0
 

Author Comment

by:Tluckett
ID: 21815975
The system was set up this way and was not intended to be used in multiple time zones. Now that it is we have to find a work around that should be simply accomplished in the query. The data is pretty straight forward

The startT and endT fields are both MYSQL Time fields formatted as 11:30:00, 15:45:00 etc. All I need is for the values in startT and endT to be added by say three hours and see if the Current time is between those times.
0
 

Accepted Solution

by:
Tluckett earned 0 total points
ID: 21818624
I have found the solution to the problem which was to use the ADDTIME() function
SELECT users.firstname, 
	users.lastname, 
	day_planner.UID,
	date_format(day_planner.endT, '%h:%i %p') as ending
FROM users LEFT OUTER JOIN day_planner ON users.UID = day_planner.UID
WHERE 
	startD = CURDATE() 
	AND 
	CURTIME() BETWEEN 
		ADDTIME(day_planner.startT, '03:00:00') 
	AND 
		ADDTIME(day_planner.endT, '03:00:00')

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL Warning Statements when you have a LIMIT clause. 6 41
Currency in SQL? 2 31
Uploading a CSV Data Import via PHP & MySql 3 33
check mysql insert 12 27
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question