Creating Localized OHLC Candles with Custom Calendars in TimescaleDB

SADEGH_NAKHJAVANI · September 6, 2024, 9:26pm

I’m working on a project that requires creating OHLC (Open, High, Low, Close) candles for various assets, including some that are specific to the Iranian market. While TimescaleDB’s MATERIALIZED VIEWS and continuous aggregation features are great for creating standard candles, I’m facing some challenges with localization. I’d appreciate any guidance on how to approach this problem.

Current Setup

I’m using MATERIALIZED VIEWS and continuous aggregation to create OHLC candles for different time frames.

Localization Challenges

Timezone: For assets like USRIRR (US Dollar against Iranian Rial) that trade only in local markets, I need to consider the Tehran timezone for candle views.
Week Start: In Iran, weeks start on Saturday, and the weekend is Friday. This affects how weekly (1w) candles should be calculated.
Persian Calendar: Monthly and yearly candles need to align with the Persian calendar (Jalali Calendar), which differs significantly from the Gregorian calendar used by default in TimescaleDB.

Questions

Is there a way to add locale information or custom calendars for Postgres / TimescaleDB when creating views?
How can I create time-based aggregations (daily, weekly, monthly, yearly) that respect a custom calendar system?
Are there any extensions or workarounds that could help implement these localization requirements?
If direct support isn’t available, what would be the best approach to implement this custom logic? Should I consider pre-processing the data or post-processing the results?

Any insights, suggestions, or examples would be greatly appreciated. Thank you in advance for your help!

jonatasdp · September 10, 2024, 3:03pm

Welcome, Sadegh

It will not be possible to introduce it using continuous aggregates but you can find a way to simulate a similar approach with materialized views or just build other hypertables on top of it as Timescale do.

Let’s double check if the code works as we expect.

First:

CREATE OR REPLACE FUNCTION gregorian_to_persian(g_date DATE) 
RETURNS DATE AS $$
DECLARE
    g_year INT := EXTRACT(YEAR FROM g_date);
    g_month INT := EXTRACT(MONTH FROM g_date);
    g_day INT := EXTRACT(DAY FROM g_date);
    jd INT;
    p_year INT;
    p_month INT;
    p_day INT;
    p_day_no INT;
BEGIN
    jd := (1461 * (g_year + 4800 + (g_month - 14) / 12)) / 4 +
          (367 * (g_month - 2 - 12 * ((g_month - 14) / 12))) / 12 -
          (3 * ((g_year + 4900 + (g_month - 14) / 12) / 100)) / 4 +
          g_day - 32075;

    p_day_no := jd - 2121445;  -- Adjusted base date
    p_year := 474 + 33 * (p_day_no / 12053);
    p_day_no := p_day_no % 12053;

    p_year := p_year + 4 * (p_day_no / 1461);
    p_day_no := p_day_no % 1461;

    IF p_day_no >= 366 THEN
        p_year := p_year + (p_day_no - 1) / 365;
        p_day_no := (p_day_no - 1) % 365;
    END IF;

    IF p_day_no < 186 THEN
        p_month := 1 + p_day_no / 31;
        p_day := 1 + (p_day_no % 31);
    ELSE
        p_month := 7 + (p_day_no - 186) / 30;
        p_day := 1 + ((p_day_no - 186) % 30);
    END IF;

    RETURN make_date(p_year, p_month, p_day);
END;
$$ LANGUAGE plpgsql;

-- Function to get Persian week start
CREATE OR REPLACE FUNCTION get_persian_week_start(p_date DATE) 
RETURNS DATE AS $$
BEGIN
    -- In Persian calendar, weeks start on Saturday
    RETURN p_date - EXTRACT(DOW FROM p_date)::INT;
END;
$$ LANGUAGE plpgsql;

-- Test the conversion
SELECT 
    d::date AS gregorian_date,
    gregorian_to_persian(d::date) AS persian_date
FROM generate_series('2024-03-20'::date, '2024-03-25'::date, '1 day'::interval) d;

Outputs:

 gregorian_date | persian_date
----------------+--------------
 2024-03-20     | 1402-01-01
 2024-03-21     | 1402-01-02
 2024-03-22     | 1402-01-03
 2024-03-23     | 1402-01-04
 2024-03-24     | 1402-01-05
 2024-03-25     | 1402-01-06

Trades example will need some extra columns to keep the data:

CREATE TABLE trades (
    time TIMESTAMPTZ NOT NULL,
    persian_date DATE NOT NULL,
    persian_week_start DATE NOT NULL,
    persian_month_start DATE NOT NULL,
    persian_year_start DATE NOT NULL,
    price NUMERIC NOT NULL,
    volume NUMERIC NOT NULL
);

Convert trades table to a hypertable

SELECT create_hypertable('trades', 'time');

Insert seed data

INSERT INTO trades (time, persian_date, persian_week_start, persian_month_start, persian_year_start, price, volume)
SELECT
    generate_series,
    gregorian_to_persian(generate_series::date),
    get_persian_week_start(gregorian_to_persian(generate_series::date)),
    DATE_TRUNC('MONTH', gregorian_to_persian(generate_series::date)),
    DATE_TRUNC('YEAR', gregorian_to_persian(generate_series::date)),
    10000 + random() * 1000,
    100 + random() * 50
FROM generate_series(
    '2024-03-20 00:00:00'::timestamp,
    '2024-03-21 23:59:59'::timestamp,
    '5 minutes'
);

Check Persian dates

SELECT DISTINCT time::date, persian_date, persian_week_start, persian_month_start, persian_year_start 
FROM trades 
ORDER BY time::date;

Outputs:

   time    | persian_date | persian_week_start | persian_month_start | persian_year_start
------------+--------------+--------------------+---------------------+--------------------
 2024-03-20 | 1402-01-01   | 1401-12-27         | 1402-01-01          | 1402-01-01
 2024-03-21 | 1402-01-02   | 1401-12-27         | 1402-01-01          | 1402-01-01
(2 rows)

Now, let’s query the hourly:

SELECT 
    time_bucket('1 hour', time) AS bucket,
    persian_date,
    first(price, time) AS open,
    max(price) AS high,
    min(price) AS low,
    last(price, time) AS close,
    sum(volume) AS volume
FROM trades
GROUP BY bucket, persian_date
ORDER BY bucket
LIMIT 5;

Outputs

     bucket         | persian_date |       open       |       high       |       low        |      close       |      volume
------------------------+--------------+------------------+------------------+------------------+------------------+-------------------
 2024-03-20 00:00:00+00 | 1402-01-01   | 10126.1308382124 | 10974.7521613309 | 10096.9054051778 | 10766.7026831495 | 2951.422285969131
 2024-03-20 01:00:00+00 | 1402-01-01   |  10012.740725261 | 10931.3653333351 | 10007.9416656508 | 10232.5458596986 | 3049.935978857528
 2024-03-20 02:00:00+00 | 1402-01-01   |  10594.198777302 | 10988.9735913038 | 10090.7268465627 | 10986.5604335756 | 3121.729686455540
 2024-03-20 03:00:00+00 | 1402-01-01   | 10757.3822765062 | 10945.0331169114 | 10010.6036836798 | 10360.8603876823 | 3100.092125050340
 2024-03-20 04:00:00+00 | 1402-01-01   | 10027.2048001866 | 10987.4102697198 | 10027.2048001866 | 10092.9718614033 | 3062.415528435267

You can now, also create the views:

CREATE MATERIALIZED VIEW hourly_ohlc AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    persian_date,
    first(price, time) AS open,
    max(price) AS high,
    min(price) AS low,
    last(price, time) AS close,
    sum(volume) AS volume
FROM trades
GROUP BY bucket, persian_date
WITH NO DATA;

-- Daily OHLC Materialized View (built on hourly)
CREATE MATERIALIZED VIEW daily_ohlc AS
SELECT
    persian_date,
    first(open, bucket) AS open,
    max(high) AS high,
    min(low) AS low,
    last(close, bucket) AS close,
    sum(volume) AS volume
FROM hourly_ohlc
GROUP BY persian_date
WITH NO DATA;

-- Weekly OHLC Materialized View (built on daily)
CREATE MATERIALIZED VIEW weekly_ohlc AS
SELECT
    date_trunc('week', persian_date) AS week_start,
    first(open, persian_date) AS open,
    max(high) AS high,
    min(low) AS low,
    last(close, persian_date) AS close,
    sum(volume) AS volume
FROM daily_ohlc
GROUP BY week_start
WITH NO DATA;

-- Monthly OHLC Materialized View (built on daily)
CREATE MATERIALIZED VIEW monthly_ohlc AS
SELECT
    date_trunc('month', persian_date) AS month_start,
    first(open, persian_date) AS open,
    max(high) AS high,
    min(low) AS low,
    last(close, persian_date) AS close,
    sum(volume) AS volume
FROM daily_ohlc
GROUP BY month_start
WITH NO DATA;

-- Yearly OHLC Materialized View (built on monthly)
CREATE MATERIALIZED VIEW yearly_ohlc AS
SELECT
    date_trunc('year', month_start) AS year_start,
    first(open, month_start) AS open,
    max(high) AS high,
    min(low) AS low,
    last(close, month_start) AS close,
    sum(volume) AS volume
FROM monthly_ohlc
GROUP BY year_start
WITH NO DATA;

A utility function to refresh them all:

CREATE OR REPLACE FUNCTION refresh_ohlc_views()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW hourly_ohlc;
    REFRESH MATERIALIZED VIEW daily_ohlc;
    REFRESH MATERIALIZED VIEW weekly_ohlc;
    REFRESH MATERIALIZED VIEW monthly_ohlc;
    REFRESH MATERIALIZED VIEW yearly_ohlc;
END;
$$ LANGUAGE plpgsql;

select refresh_ohlc_views();

Then, checking one by one:

table hourly_ohlc;
        bucket         | persian_date |       open       |       high       |       low        |      close       |      volume
------------------------+--------------+------------------+------------------+------------------+------------------+-------------------
 2024-03-20 05:00:00+00 | 1402-01-01   | 10480.2859312987 | 10998.8581005023 | 10034.0242150106 | 10076.7072760792 | 7274.182169492257
 2024-03-21 20:00:00+00 | 1402-01-02   | 10609.3298491125 | 10977.9898605464 | 10002.3406159785 | 10289.0309731327 | 7677.733864102888
 2024-03-21 02:00:00+00 | 1402-01-02   | 10117.6377522829 | 10959.0688020875 | 10062.1875305572 | 10237.2422163911 | 7460.108640474240
 2024-03-21 07:00:00+00 | 1402-01-02   | 10254.2309639444 | 10950.5241652354 | 10029.4467315904 | 10471.6490002814 | 7554.729227289559
 2024-03-21 14:00:00+00 | 1402-01-02   | 10321.6598153598 | 10991.0738360172 | 10041.4004205388 | 10498.5304382217 | 7391.458590624941
 2024-03-21 17:00:00+00 | 1402-01-02   |  10284.588672725 |   10999.82560008 | 10009.3783896248 |  10682.043098512 | 7527.742387318790
 2024-03-21 05:00:00+00 | 1402-01-02   | 10076.0759825285 | 10994.7004898284 | 10021.4022437493 | 10645.8325414366 | 7551.077179884907
 2024-03-20 09:00:00+00 | 1402-01-01   | 10630.5874566314 | 10976.8596865026 |  10010.991267649 | 10641.4217783699 | 7557.340015347377
 2024-03-21 03:00:00+00 | 1402-01-02   | 10730.4983159822 | 10986.7569953966 | 10049.3210642175 | 10759.6725787434 | 7505.076578356057
 2024-03-20 12:00:00+00 | 1402-01-01   | 10883.9101822369 | 10999.2372561589 |  10010.411910259 | 10538.6778211056 | 7344.545709184578
 2024-03-20 02:00:00+00 | 1402-01-01   |  10594.198777302 | 10988.9735913038 |  10000.414770293 | 10986.5604335756 | 7447.203568679007
 2024-03-20 01:00:00+00 | 1402-01-01   |  10012.740725261 | 10993.8386590225 | 10001.9674466514 | 10232.5458596986 | 7670.970248703810
 2024-03-20 00:00:00+00 | 1402-01-01   | 10126.1308382124 | 10996.2956948799 | 10010.0867055983 | 10766.7026831495 | 7558.333115927642
 2024-03-21 11:00:00+00 | 1402-01-02   |  10663.230336549 |  10982.686610116 | 10001.0648636142 | 10763.8430108273 | 7485.639394357749
 2024-03-21 18:00:00+00 | 1402-01-02   | 10757.2533765737 | 10982.8288232915 | 10037.0616179849 |  10298.645512383 | 7555.985758925655
 2024-03-20 13:00:00+00 | 1402-01-01   | 10050.9257650584 |  10987.947507393 | 10020.5189248402 | 10248.8204858932 | 7593.805934384043
 2024-03-20 06:00:00+00 | 1402-01-01   | 10293.9282596498 | 10980.6634261257 | 10004.3897818311 | 10163.2257018122 | 7683.795939871996
 2024-03-21 15:00:00+00 | 1402-01-02   | 10755.4252567316 | 10996.6243321432 | 10005.3504236687 | 10582.1530086519 | 7464.514475829700
 2024-03-21 13:00:00+00 | 1402-01-02   | 10038.6887424765 |  10995.604996389 | 10003.8270293096 |  10063.682257908 | 7406.193718753656
 2024-03-20 15:00:00+00 | 1402-01-01   | 10194.9632377202 | 10981.7021695356 | 10003.4143483742 | 10003.4143483742 | 7566.033932069375
 2024-03-21 19:00:00+00 | 1402-01-02   | 10375.2828144667 | 10977.8074715461 | 10004.9175262723 | 10910.0700536646 | 7510.205430215568
 2024-03-20 14:00:00+00 | 1402-01-01   |   10884.52286537 | 10981.6822175998 | 10001.9905835983 | 10296.8291144152 | 7502.862580954947
 2024-03-20 03:00:00+00 | 1402-01-01   | 10757.3822765062 | 10997.0391474644 | 10010.6036836798 | 10360.8603876823 | 7617.726433072240
 2024-03-21 00:00:00+00 | 1402-01-02   | 10648.6804666045 | 10986.9493727572 | 10000.8734325132 | 10673.7126315358 | 7317.649814759660
 2024-03-21 22:00:00+00 | 1402-01-02   | 10642.1418663821 | 10997.4929849323 | 10041.6643594391 | 10159.2327167547 | 7447.113032471581
 2024-03-21 09:00:00+00 | 1402-01-02   | 10319.9445654485 | 10981.8191366683 |  10000.364231599 | 10003.0414678266 | 7348.595981424270
 2024-03-20 22:00:00+00 | 1402-01-01   |  10701.950259371 | 10982.3905792463 | 10010.5600772625 | 10870.2595214205 | 7398.013369235063
 2024-03-20 11:00:00+00 | 1402-01-01   | 10192.7788715317 | 10994.3834273553 | 10002.4437502697 | 10928.2819372446 | 7662.492408615872
 2024-03-21 01:00:00+00 | 1402-01-02   | 10765.1855517836 | 10991.6993321703 | 10012.7954684196 | 10451.0528348005 | 7499.883318463332
 2024-03-20 10:00:00+00 | 1402-01-01   | 10303.0267667948 | 10974.0987496543 | 10028.3932940777 | 10613.7867531295 | 7526.970638505675
 2024-03-20 04:00:00+00 | 1402-01-01   | 10027.2048001866 | 10987.4102697198 | 10003.2960650517 | 10092.9718614033 | 7645.044843525731
 2024-03-21 16:00:00+00 | 1402-01-02   | 10936.8800896474 | 10994.3329752909 | 10041.1293748157 | 10606.2412169094 | 7499.961594752040
 2024-03-20 18:00:00+00 | 1402-01-01   |   10018.63408085 | 10994.9647042494 | 10004.8104517294 | 10457.8415674105 | 7605.627243006056
 2024-03-21 06:00:00+00 | 1402-01-02   | 10175.8176044883 | 10998.6438602682 | 10028.3379799717 | 10954.2536860137 | 7530.306354456640
 2024-03-21 21:00:00+00 | 1402-01-02   | 10220.3925735684 | 10995.7494041831 | 10003.1368481107 |  10228.734699307 | 7650.422328699226
 2024-03-21 23:00:00+00 | 1402-01-02   | 10822.4957323538 | 10989.6671673658 | 10008.6754096207 | 10244.9634913668 | 7436.584091086779
 2024-03-21 08:00:00+00 | 1402-01-02   | 10741.7626217005 | 10983.2154057534 | 10006.8763857612 | 10134.5560924016 | 7497.090462587403
 2024-03-20 07:00:00+00 | 1402-01-01   | 10221.5754428763 | 10997.4345117572 | 10009.2348122346 |  10728.891425597 | 7716.353440417681
 2024-03-20 21:00:00+00 | 1402-01-01   | 10827.4695562064 | 10993.7386906764 | 10000.2330497491 | 10687.5795495702 | 7504.147654112892
 2024-03-20 20:00:00+00 | 1402-01-01   | 10648.2112796907 | 10957.2159737267 | 10018.5951442908 | 10180.7452611721 | 7601.730357164119
 2024-03-20 23:00:00+00 | 1402-01-01   | 10631.9879139534 | 10996.5528229047 | 10002.4819569176 | 10366.0452679546 | 7598.815138062800
 2024-03-20 17:00:00+00 | 1402-01-01   | 10934.5914968058 | 10989.1286426424 | 10009.3974592549 | 10234.9948428373 | 7670.286781442319
 2024-03-20 08:00:00+00 | 1402-01-01   | 10203.0978558365 | 10942.7926722812 | 10002.5181581271 | 10930.7762744344 | 7548.885359774191
 2024-03-21 04:00:00+00 | 1402-01-02   | 10412.1656843603 | 10995.5956107049 | 10001.0665085057 | 10922.5993919902 | 7572.314990801407
 2024-03-20 19:00:00+00 | 1402-01-01   | 10598.4605396214 | 10974.1641068176 | 10041.4823301205 | 10363.6858102833 | 7484.896977291921
 2024-03-20 16:00:00+00 | 1402-01-01   | 10163.1534039887 | 10998.8709575054 | 10023.5846462935 | 10808.5424801818 | 7663.726703707910
 2024-03-21 12:00:00+00 | 1402-01-02   | 10962.5532377297 | 10987.7626948928 | 10011.6397753509 | 10574.2877578802 | 7471.534954907314
 2024-03-21 10:00:00+00 | 1402-01-02   | 10985.7133044064 | 10985.7133044064 | 10010.5262044661 | 10541.1197442554 | 7470.898562664825
(48 rows)

Outputs:

Daily

table daily_ohlc;

Outputs:

 persian_date |       open       |       high       |       low        |      close       |       volume
--------------+------------------+------------------+------------------+------------------+---------------------
 1402-01-02   | 10648.6804666045 |   10999.82560008 |  10000.364231599 | 10244.9634913668 | 179832.820733208187
 1402-01-01   | 10126.1308382124 | 10999.2372561589 | 10000.2330497491 | 10366.0452679546 | 181443.790562549502
(2 rows)

Weekly

table weekly_ohlc;

Outputs:

       week_start       |       open       |      high      |       low        |      close       |       volume
------------------------+------------------+----------------+------------------+------------------+---------------------
 1401-12-28 00:00:00+00 | 10126.1308382124 | 10999.82560008 | 10000.2330497491 | 10244.9634913668 | 361276.611295757689
(1 row)

Monthly

table monthly_ohlc;

Outputs:

      month_start       |       open       |      high      |       low        |      close       |       volume
------------------------+------------------+----------------+------------------+------------------+---------------------
 1402-01-01 00:00:00+00 | 10126.1308382124 | 10999.82560008 | 10000.2330497491 | 10244.9634913668 | 361276.611295757689
(1 row)

SADEGH_NAKHJAVANI · September 24, 2024, 6:12pm

Thank you a lot for you response, I will check it and send feedback here…