How to deal with PHP + MySQL timezones and MySQL GROUP BY [date] queries? -



How to deal with PHP + MySQL timezones and MySQL GROUP BY [date] queries? -

i'm working drupal 7, php, , mysql, , trying head around timezone issues when querying mysql database.

drupal handles dates pretty out of gate: dates stored utc timestamp int's in db, , timezone conversion happens on per-user basis via timezone setting in each user profile, using php 5's built-in timezone capabilities (so each time php script run script's timezone set current user's timezone).

so fine , dandy , painless long stick php.

things start tricky when bring in mysql, since there doesn't appear way synchronize current php script timezone given mysql query. seems best practice dictates handling timezone conversion in php: ever querying database raw timestamps, , converting in php necessary.

this seems reasonable in cases (even if bit slower @ times), but supposed mysql grouping [date] queries? instance, i'm building module handle analytics, , want things like:

group year(from_unixtime(u.created)), month(from_unixtime(u.created))

so run timezone issue...

possible solutions have come mind:

hard-code timezone: utilize date_default_timezone_set() within module insure php timezone set scheme timezone (so mysql timezone = php timezone). in other words, analytics timezone hard-coded, rather respecting timezone of user viewing analytics. isn't ideal, since want users in multiple timezones able access analytics using timezones. also, date_default_timezone_set() seems mess drupal up, sets timezone entire script, instead of within particular function...

forget using grouping in queries: fetch raw info db (be there tens or hundreds of thousands of rows), grouping results date in php using loops... solution seems more resource intensive, slower, , ridiculous.

so guess i'm asking is, have missed something? there best practice here i'm not aware of?

thanks ton help!

i consider approach such this

set time_zone = '+02:00';

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

and have

group from_unixtime(u.created, '%y-%m');

since from_unixtime bases it's time on time_zone, should give desired result.

to undo time_zone alter afterwards, consider saving select timediff(now(), convert_tz(now(), @@session.time_zone, '+00:00')); first , set saved value afterwards.

php mysql drupal datetime timezone

Comments

Post a Comment

Popular posts from this blog

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer -

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer - when trying request rest service @ time of response getting exception.. feb 21, 2013 2:34:49 pm com.sun.jersey.spi.container.containerresponse mapmappablecontainerexception severe: exception contained within mappablecontainerexception not mapped response, re-throwing http container java.lang.noclassdeffounderror: not initialize class net.sf.cglib.proxy.enhancer @ org.hibernate.proxy.pojo.cglib.cgliblazyinitializer.getproxyfactory(cgliblazyinitializer.java:117) @ org.hibernate.proxy.pojo.cglib.cglibproxyfactory.postinstantiate(cglibproxyfactory.java:43) @ org.hibernate.tuple.entity.pojoentitytuplizer.buildproxyfactory(pojoentitytuplizer.java:162) @ org.hibernate.tuple.entity.abstractentitytuplizer.<init>(abstractentitytuplizer.java:135) @ org.hibernate.tuple.entity.pojoentitytuplizer.<init>(pojoentitytuplizer.java:55) @ org.hibernate.tuple.e...
Read more

Accessing MATLAB's unicode strings from C -

Accessing MATLAB's unicode strings from C - how can access underlying unicode info of matlab strings through matlab engine or mex c interfaces? here's example. let's set unicode characters in utf-8 encoded file test.txt, read as fid=fopen('test.txt','r','l','utf-8'); s=fscanf(fid, '%s') in matlab. now if first feature('defaultcharacterset', 'utf-8') , c engevalstring(ep, "s") , output text file utf-8. proves matlab stores unicode internally. if mxarraytostring(enggetvariable(ep, "s")) , unicode2native(s, 'latin-1') give me in matlab: non-latin-1 characters replaced character code 26. need getting access underlying unicode info c string in unicode format (utf-8, utf-16, etc.), , preserving non-latin-1 characters. is possible? my platform os x, matlab r2012b. addendum: documentation explicitly states "[mxarraytostring()] supports multibyte encoded characters...
Read more

javascript - mongodb won't find my schema method in nested container -

javascript - mongodb won't find my schema method in nested container - i trying access method of schema stored within mixed container. here situation : have cases model can many different things, have schema each of these things stored in "casecontent" mixed property. var caseschema = mongoose.schema({ casecontent : {}, object : {type:string, default : "null"}, collision : {type : boolean, default : false} }); the casecontent property filled model of 1 of schemas, 1 exemple : var treeschema = new mongoose.schema({ applecount : {type : number, default : 3} }); treeschema.methods.dostuff = function (data) { console.log('hey, listen'); homecoming true; }; then, want utilize method of schema original container : caseschema.methods.dostuff = function (data) { if (this.casecontent.dostuff !== undefined) { this.casecontent.dostuff(); console.log('it worked'); } else { ...
Read more