Time is a central concept everywhere — in e-commerce, as it is in life. Some data architects have even argued that non-temporal data is a “trivial extension” of temporal data.
Knowing how long a transaction takes or fails to complete its entire life cycle can produce insights into the order flow pipeline, as well as facilitate reconciliation.
Similarly, knowing what time of day, or what day of the week, or what month of the year that customers shop most, can give store owners the ability to discern purchasing patterns that would otherwise go unnoticed.
Unfortunately, obtaining normalized, temporal information is not always the easiest thing to do.
A credit card processor may use UTC time to indicate when a transaction took place. UTC time is convenient in terms of data processing, but it does very little to enhance a merchant’s understanding of localized temporal shopping trends.
It would be simplistic to say that a one statement solution would be sufficient to convert everything to normalize. An example of a naive temporal query is as follows:
select UTC_TIMESTAMP() - now();
A WC-plugin programmer has to infer a merchant’s actual timezone if he or she does not know in advance where the application will run.
Algorithms that take into account a global merchant’s entire temporal ecosystem can get quite complex.
Such a merchant may have customers located in any time zone around the world, or have a geo-distributed platform, in which time zones support daylight savings times vary.
There is also always the possibility — however remote, but a consideration that a completist developer would take into account — that an operating system may be set to one-time zone, the RDBM(S) running under it to another (which can be further complicated by distributed master-slave server deployments), and the runtime programming language (such as PHP) is defaulting to yet another TZ setting.
It is therefore obvious that some means must be found to determine events in equivalent time: for example, the local (to the customer) time that a customer purchased an item, and the local (to the merchant) time that funds became available in their treasury account(s).
My goal in this post is to create a simple function CONVERT_UTC_2_LOCALT that converts a UTC time value that is found in Stripe’s payment file to a localized one.
Here are the (ideal) requirements:
1. It must convert UTC time for the current year (at time of coding, this is 2019)
2. It must also convert UTC time for the previous year
3. The conversion must work irrespective of an end user’s local timezone
4. Conversion must take into account DST (in the US, and, after testing, in Europe and perhaps elsewhere)
5. If UTC values are outside this range, then the local time bucket is set to being identical to UTC
6. The entire function must be written in SQL
7. Must be tested against a sufficiently rich data set for performance evaluation
Here is a read-only, key | value table that is useful as a fast lookup of hardcoded data values.
CREATE TABLE sb_temporality ( sb_meta_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, sb_meta_key VARCHAR(255) NOT NULL, sb_event_marker_UTC DATE NULL, sb_ordinal_date_UTC SMALLINT NULL, -- aka "Julian date" PRIMARY KEY ( sb_meta_id ) ); CREATE UNIQUE INDEX sb_tempindx ON sb_temporality(sb_meta_key);
Here’s the data contained in this “helper” table.
// 2019 DST rows INSERT sb_temporality VALUES( 0, '2019_start_dst_us', '2019:03:10 02:00:00', DAYOFYEAR('2019-03-10') ); INSERT sb_temporality VALUES( 0, '2019_end_dst_us', '2019:11:03 02:00:00', DAYOFYEAR('2019-011-03') ); // 2018 DST rows INSERT sb_temporality VALUES( 0, '2018_start_dst_us', '2018:03:11 02:00:00', DAYOFYEAR('2018-03-11') ); INSERT sb_temporality VALUES( 0, '2018_end_dst_us', '2018:11:04 02:00:00', DAYOFYEAR('2019-011-04') );
Note that rows can easily be added to support additional time zones, or other sorts of calculations that need quick lookup values; for example, to support domains that require, say, decimal values or other data types.
Here’s the stored function.
DELIMITER // CREATE OR REPLACE FUNCTION CONVERT_UTC_2_LOCALT(Created_UTC DATETIME) RETURNS DATETIME DETERMINISTIC BEGIN /* variables */ DECLARE now, maintenant, local_t DATETIME; DECLARE curr_start_dst, curr_end_dst, prev_start_dst, prev_end_dst SMALLINT; /* initialization */ SET now = now(); SELECT sb_ordinal_date into curr_start_dst from sb_temporality where sb_meta_key = '2019_start_dst_us'; SELECT sb_ordinal_date into curr_end_dst from sb_temporality where sb_meta_key = '2019_end_dst_us'; SELECT sb_ordinal_date into prev_start_dst from sb_temporality where sb_meta_key= '2019_start_dst_us'; SELECT sb_ordinal_date into prev_end_dst from sb_temporality where sb_meta_key = '2019_end_dst_us'; /* mainline */ -- current year, within DST interval IF ( YEAR(Created_UTC) = YEAR(now) ) AND ( DAYOFYEAR(Created_UTC) BETWEEN curr_start_dst AND curr_end_dst ) THEN SELECT (CONVERT_TZ(Created_UTC,'+00:00', LEFT(timediff( now(), utc_timestamp() ), 6)) ) into maintenant; END IF; -- current year, outside DST interval IF ( YEAR(Created_UTC) = YEAR(now) ) AND ( DAYOFYEAR(Created_UTC) NOT BETWEEN curr_start_dst AND curr_end_dst ) THEN SELECT (CONVERT_TZ(Created_UTC,'+00:00', LEFT(timediff( now(), utc_timestamp() ), 6)) - INTERVAL 1 HOUR ) into maintenant; END IF; -- previous year, within DST interval IF ( YEAR(Created_UTC) = (YEAR(now) + INTERVAL -1 YEAR ) ) ) AND ( DAYOFYEAR(Created_UTC) BETWEEN prev_start_dst AND prev_end_dst ) THEN SELECT (CONVERT_TZ(Created_UTC,'+00:00', LEFT(timediff( now(), utc_timestamp() ), 6)) ) into maintenant; END IF; -- previous year, outside DST interval IF ( YEAR(Created_UTC) = (YEAR(now) + INTERVAL -1 YEAR ) ) AND ( DAYOFYEAR(Created_UTC) NOT BETWEEN prev_start_dst AND prev_end_dst ) THEN SELECT (CONVERT_TZ(Created_UTC,'+00:00', LEFT(timediff( now(), utc_timestamp() ), 6)) - INTERVAL 1 HOUR) into maintenant; END IF; RETURN maintenant; END //
Now all you have to do is run the following query.
UPDATE sb_etrans SET Created_localtime = CONVERT_UTC_2_LOCALT(Created_UTC);
In this implementation, CONVERT_UTC_2_LOCALT(datetime) handles DST and non-DST intervals for “-0500” UTC offsets (which includes EST). It performs temporal normalization on transactions that were generated during 2018-19. Thanks to the sb_temporality table, it’s a straightforward exercise to extend the TZ conversion to other countries or date ranges.
Here’s the before and after.
CONVERT_UTC_2_LOCALT(datetime) greatly simplifies normalizing UTC, and can easily be added to a script that loads Stripe data to the appropriate StoreBoss table on the WP server.