How To Select Time Range with Timezone in MySQL

A common topic we cover is how the ability to select portions of data based on criteria such as date. For example, in Cloud Control (www.cldctrl.com) we have users from all across the world. So, when we want to show a user their traffic activity for a given period, we need to not only select the data from the needed range, but we need to to shift the time to apply to their timezone. This gives us the ability to show the user metrics of their visitor engagement in comparison to each other and see what trends are occurring in real time. What you need is a user setting (or an automatic setting based on their geoIP), which gives you the desired timezone in UTC. In this example we are using UTC -8 (Pacific Standard Time).
$timezone = "8";
$start_date = date("Y-m-d H:i:s", strtotime($start));
$end_date = date("Y-m-d H:i:s", strtotime($end));

query("SELECT   my_data,
                entry_date
                FROM my_table
                AND DATE_SUB(entry_date, INTERVAL $timezone HOUR) >= '$start_date'
                AND DATE_SUB(entry_date, INTERVAL $timezone HOUR) <= '$end_date' ");
When a visitor arrives at one of the Cloud Control tracked websites, it logs all the website engagement data in the database in standard GMT time, regardless of the users actual timezone. This gives us the ability for our users to later change timezones for any reason, without their data becoming offset. It is important to always keep your data standard, and then do the manipulation on the layer above it, instead of actually modifying any of your data in the database. Using MySQL DATE_SUB, we display visitors arriving at our users site with a time stamp that if they looked down at their computer clock, they would see "oh! This visitor just came to my site 2 minutes ago!"
Small dog web development studio

View All Our Posts

Web application developers in western Washington, creating user centric web solutions for businesses of all shapes and sizes.

Our projects include ecommerce, event organization, user driven web applications and more.


Small Dog Studios, LLC. Small Dog Studios News