StoreBoss™ relies on three important temporal data sources:
- the WooCommerce (WC) DB;
- Stripe transactional data; and
- a WC store owner’s bank Statement of Account.
StoreBoss performs a “tick-and-tie” matching operation between these sources of transactional data. Discrepancies may arise due to differences in time settings.*
DST adds a further complication, by causing transactional time to be shifted on recurring intervals, depending on which TZ a store owner has set for his or her WP installation.
Per Stripe’s documentation,
TIME ZONE DIFFERENCE
All payments and payouts are processed according to UTC time. As a result, the processed date may not be the same as your local time zone.
A desirable solution is to normalize the Stripe charge date by extending MariaDB (or MySQL) with a stored function that automatically and consistently performs a correction in all use cases.
By applying this function to produce a new temporal value (which is persisted in a custom descriptor during file loads), StoreBoss can calculate normalized lag times between the various servers that produce transactional data.
Thus, StoreBoss will be able to more accurately verify a store owner’s Cash Balance Ledger position.
Let’s get into some technical details.
Assume you have downloaded from Stripe the previous day’s transactions, and manually load it to a work table whose structure matches the file’s. While creating the necessary DDL, you might notice that a field named “Created(UTC)” is used by Stripe to indicate when news of the transaction was conveyed to your bank.
After uploading the CSV file (which happens to contain 1 record) to a work table on Maria whose structure matches this file, you decide to create a view that contains a column that shows the time of the event in local units. The code for such a view that produces the local time translation for any global time zone would look something like this.
CREATE VIEW IF NOT EXISTS `sb_etrans_vw`AS SELECT ... Created_UTC AS 'CreateD UTC', CONVERT_TZ(Created_UTC,'+00:00', LEFT(timediff( now(), utc_timestamp() ), 6) ) AS 'Event instance', ... FROM sb_etrans;
As you can see below, this code does its job perfectly well — except for two fatal flaws… it does not check to see if a transaction falls within the DST interval for your timezone, whose start and end date (expressed as a number, not as a weekday) changes yearly.
For example, the second Sunday of March in 2019 was March 10, and the first Sunday of November will be the 3rd. In 2018, these dates were March 11th, and 4th.
To facilitate its use, the modified code could be encapsulated, as follows:**
CREATE VIEW IF NOT EXISTS `sb_etrans_vw`AS SELECT ... Created_UTC AS 'CreateD UTC', CONVERT_UTC_2_LOCALT(Created_UTC) AS 'Event instance', ... FROM sb_etrans;
We shall be exploring in some depth CONVERT_UTC_2_LOCALT.
In future posts, I will explore Stripe’s Sigma product, which allows WC store owners) to perform extensive data analysis.
Sigma may be a preferred way for SQL-savvy end users to obtain needed data from Stripe — without having to go through Stripe’s REST API and JSON file decoding and OAuth authorizations.
To be sure, Sigma is not free; to many SMB WC storeowners, its price point — particularly in view of Sigma’s significant data lag — may not be worth $500 a year.
Moreover, the hurdle of its SQL interface can be quite intimidating to a nontechnical end-user.
Having said all that, let’s write the UTC conversion function for our manually-downloaded CSV STRIPE payments file.
*UTC has been in the news of late!
**Realistically you would only have to account for the last 2 years of transactions, on a rolling basis, as this is typically a banking archive limit