Convert to client timezone

[pantonis]

I have a table with millions of rows and a Timestamp column which hold datetime with milliseconds in UTC.

We have a SaaS where clients are connecting from different timezones and thus we need to convert to local timezone of the client. 

When we use queries like group by month, year, quarter and perform aggregations etc.., we need to convert to client timezone and we use CONVERT_TZ to convert and we see huge performance decrease. 

SELECT
    MONTH(CONVERT_TZ(Timestamp, '+00:00', '+03:00')) AS Month,
    Asset,
    SUM(Profit) AS Profit,
    SUM(Margin) AS Margin
FROM Order
LEFT JOIN DimAsset ON Order.DimAssetKey = DimAsset.DimAssetKey
WHERE
    (
        Order.Timestamp  >= CONVERT_TZ("2023-04-01T21:00:00", '+00:00', '+02:00')
        and Order.Timestamp  <= CONVERT_TZ("2023-04-05T20:59:59", '+00:00', '+02:00')
    )
GROUP BY Month, Asset
LIMIT 51 OFFSET 0

Is there any way to optimize the above?

[Allen Herrera]

avoid the function calls on columns, (MONTH(CONVERT_TZ) and do this on an outer select after the subquery does all the aggregation 
or if you do the same converts/ only a couple finite converts, create new columns where do you the math/ calculation beforehand since storing extra columns is cheap to index the calculation
or
run the converts on your front end UI to shift the timezone/hours

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/8d60328a-81d4-4a37-a1fc-7c337885babfn%40googlegroups.com.

--

Allen Herrera

Customer Engineer | MariaDB Corporation

+1.360.888.3938 | calendly.com/allen-herrera | allen.herrera@mariadb.com

[Allen Herrera]

For example, avoid MONTH(timestamp) and save the billions of cpu cycles on every row and just store the month as an int in an additional column per row.
so that you can select month. this will save cpu and allow extent elimination to optimize your queries with min/max values per extent

[pantonis]

Allen thanks for your reply.

"avoid the function calls on columns, (MONTH(CONVERT_TZ) and do this on an outer select after the subquery does all the aggregation " What do you mean?

We already have a DimDate table but we don't use that for date grouped aggregations (Month, Quarter, Year summary) due to different timezones. Data in database are stored in UTC. If a client which is on UTC+5 wants to see a report on his timezone we won't be able to view correct data because times is in UTC and if we do aggregations based on UTC (DimDate table) which is ultra fast, we wont be viewing correct data as the aggregations will be in UTC (not his local timezone) e.g for a month summary report since his timezone is +05:00 he will be viewing data that are falling into the range of previous month based on UTC time of the database. 

"run the converts on your front end UI to shift the timezone/hours" How can this be done when aggregations are done on database and also month names are produced there.

[Allen Herrera]

1)  im slapping this together quickly just to show the idea, syntax probably not 100% right
select CONVERT_TZ(Timestamp, '+00:00', '+03:00') AS Month,
 ( SELECT
     MONTH(Timestamp) AS Month

    Asset,
    SUM(Profit) AS Profit,
    SUM(Margin) AS Margin
FROM Order
LEFT JOIN DimAsset ON Order.DimAssetKey = DimAsset.DimAssetKey
WHERE
    (

        Order.Timestamp  >= 2023-04-01T21:00:00" 
        and Order.Timestamp  <= 2023-04-05T20:59:59"

    )
GROUP BY Month, Asset
LIMIT 51 OFFSET 0

) as tmp

* adjust the time in the where clause as appropriate +2 versus +3 in select
** this could be further improved if you could store a new column thats called month, MONTH(Timestamp) 


2/3) After doing the aggregation, save the data into a temporary result and then adjust the timezone(  maybe a tmp table then select out transforming the dates) , or write a script in some language, node, python, php, java, whatever you're comfortable with to adjust the time. if a webpage, vue.js and other frameworks have libraries to adjust datetime relevant to the user.
This is more work up front to engineer/ code but can yield a faster overall process.


I'm happy to help with simple, quick issues as I'm limited to how much I can help here. For anything more complex or detailed, please create a support ticket with your enterprise account. 

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/9eb5e733-91b8-426f-8cad-3b0462dc95a3n%40googlegroups.com.

[pantonis]

Hello Allen and thanks again.

We cannot do that and let me explain why.

lets say we are on GMT+3 and we want the summary by month report for 2023

that means that if we do grouping by UTC Timestamp (which is saved in UTC in the tables) without converting for the gap between 2023-01-31 21:00:00 UTC until  2023-01-31 23:59:59 UTC query will return profit for January whereas we run the report and expecting the results on Client Timezone +3

lets say we have 3 orders:

1st order Profit 100 on 2023-01-31 07:00 UTC

2nd order Profit 200 on 2023-01-31 23:00 UTC

3rd order Profit 400 on 2023-02-01 10:00 UTC

if we run it without converting to client timezone user will see

January 300

February 400

Whereas the user will see wrong result because 2nd order is in February based on his client timezone and he should see

January 100

February 600

Since the aggregations are done on db we cannot do anything about this on client unless we return all orders on client and do the aggregations there. which is not the case 

[Allen Herrera]

does dynamically adjusting the where clause based on the users timezone as i mentioned accommodate for whats considered in the month for said user?
user 1

  Order.Timestamp  >= 2023-04-01T21:00:00"
   and Order.Timestamp  <= 2023-04-05T20:59:59"

vs
user 2
  Order.Timestamp  >= 2023-04-01T20:00:00"
   and Order.Timestamp  <= 2023-04-05T19:59:59"

Also I mentioned a finite number of conversions for new columns if possible. meaning instead of just a single month column, you could have a GMT3_month column versus a GMT2_month column versus GMT1_month etc etc. definitely works if you need just a handful. but if you have customers from all timezones or maybe global users, then trade offs need to be evaluated of course. pre-calculations versus post-calculations.

I'm limited to how much I can help here but I'm happy to help with simple, quick issues, tips or ideas.

For anything more complex or detailed, please create a support ticket with your enterprise account. 

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/273bba1f-d5e6-4c9f-b268-cb80fdda7de8n%40googlegroups.com.

[pantonis]

where should be on UTC always since time in database is in UTC we convert  input  from client (client timezone) to UTc

We have a lot of timezones and maintaining a table for each timezone is not good. We will end up writing timezone columns isntead of business data.