3

I have the following tables and relevant columns:

state
    id int
    name text
    utc_offset int (minutes)

sale
    id int
    state_id int
    created datetime
    amount decimal(8,2)

The value put into the created column is the local server time, which is in state id = 1.

How can I get the total amount of sales for each day for each state?

Is there a better way to handle timezone data?

Bohemian
  • 412,405
  • 93
  • 575
  • 722

2 Answers2

2

I don't know MySQL syntax by heart, but is this on the right track?

SELECT sale.name AS name, 
    adddate(sale.created. interval (state.utc_offset - (select utc_offset from state where id = 1)) minute) AS date, 
    SUM(amount) AS total_amount
FROM sale
JOIN state ON sale.state_id = state.id
GROUP BY name,date;

NOTE: Storing a utc_offset like this is sloppy, at best, as the utc_offset changes twice a year in many parts of the world. It is usually much better practice to store a timezone name, and use an actual timezone manipulation library to do your date calculations for you.

EDIT

My suggestion for timezone handling would be to modify your state table as follows:

state
    id int
    name text
    tz varchar

Then update your query as follows:

SELECT sale.name AS name,
    CAST(CONVERT_TZ(sale.created,'utc',state.tz) AS DATE) AS date,
    SUM(amount) AS total_amount
FROM sale
JOIN state ON sale.state_id = state.id
GROUP BY name,date;
Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
  • I don't think there is built in timezone support in mysql, but I could be wrong. I'd be interested to hear from anyone who knows – Bohemian Jan 02 '13 at 12:51
  • @Bohemian: It looks like MySQL does have some support for that. See [this question](http://stackoverflow.com/q/2187593/13860). – Jonathan Hall Jan 02 '13 at 12:53
  • OK so `CONVERT_TZ()` plus storing the *name* of the timezone would do the trick. Thanks. – Bohemian Jan 02 '13 at 12:55
  • @Bohemian: Indeed. I updated my answer with a query that might do the trick (untested, as I still don't have MySQL running here) – Jonathan Hall Jan 02 '13 at 12:58
2

Your solution won't work for states with more than one timezone.

Generally, best practice is to store every datetime as UTC in your database. You then use the client's timezone (usually readily available in web apps) to format it for display to the user in their own local timezone.

So you wouldn't be storing state time offsets and your sales per day by state query would be far more straightforward.

Mike Parkhill
  • 5,511
  • 1
  • 28
  • 38
  • One could also migrate to a 'site' table in place of the 'state' table, with the state as one column. This would allow an arbitrary number of timezones per state, and still allow grouping by state. – Jonathan Hall Jan 02 '13 at 13:02
  • Do you know of an example of a state anywhere in the world that has more than one timezone? I haven't heard if any, and I live in Australia which has the largest state in the world: Western Australia and it has only one timezone – Bohemian Jan 02 '13 at 13:23
  • Kansas, where I live, has two timezones. The western most part is in U.S. Mountain time, the rest in Central time. Several Mexican states along the U.S. border have regions that share timezones with the U.S., while the rest of the state is in a Mexican timezone. Arizona has two timezones half of the year, as only a small part of the state uses DST. A quick glance [here](http://www.timetemperature.com/tzus/new_mexico_time_zone.shtml) shows at least 11 U.S. states with multiple timezones. I know there are other examples – Jonathan Hall Jan 02 '13 at 13:38
  • I believe the USA has 13 states that cross timezones, Canada has a couple provinces that span timezones as well. – Mike Parkhill Jan 02 '13 at 14:38