2 replies

Fetch data based on client's timezone.

Currently, I have a postgres db, where my timestamp data is being saved as a

timestamp with timezone

timestamp with timezone

type, with

+0

+0

timezone. I've been fetching my data with the code below

    const statement = sql`
      WITH date_series AS (
        SELECT generate_series(
          NOW() - INTERVAL '${sql.raw((days - 1).toString())} DAYS', 
          NOW(), 
          '1 day'::interval
        )::date AS date
      )
      SELECT 
        ds.date,
        COALESCE(SUM(${orders.totalPrice}), 0) AS revenue
      FROM date_series ds
      LEFT JOIN ${orders} ON date_trunc('day', ${orders.createdAt}) = ds.date
      GROUP BY ds.date
      ORDER BY ds.date ASC;
    `;

    const statement = sql`
      WITH date_series AS (
        SELECT generate_series(
          NOW() - INTERVAL '${sql.raw((days - 1).toString())} DAYS', 
          NOW(), 
          '1 day'::interval
        )::date AS date
      )
      SELECT 
        ds.date,
        COALESCE(SUM(${orders.totalPrice}), 0) AS revenue
      FROM date_series ds
      LEFT JOIN ${orders} ON date_trunc('day', ${orders.createdAt}) = ds.date
      GROUP BY ds.date
      ORDER BY ds.date ASC;
    `;

The problem I have, is that the system will group the data based on the DB server's timezone (in this case +0).

How can i update the code so that the data returned will always follow the client's timezone? For example, If the client is in UTC +9, then it should group the date after offsetting the timestamps by 9 hours.

Drizzle Team•2y ago•

2 replies

Rorsch

Fetch data based on client's timezone.

Currently, I have a postgres db, where my timestamp data is being saved as a

timestamp with timezone

timestamp with timezone

type, with

+0

+0

timezone. I've been fetching my data with the code below

    const statement = sql`
      WITH date_series AS (
        SELECT generate_series(
          NOW() - INTERVAL '${sql.raw((days - 1).toString())} DAYS', 
          NOW(), 
          '1 day'::interval
        )::date AS date
      )
      SELECT 
        ds.date,
        COALESCE(SUM(${orders.totalPrice}), 0) AS revenue
      FROM date_series ds
      LEFT JOIN ${orders} ON date_trunc('day', ${orders.createdAt}) = ds.date
      GROUP BY ds.date
      ORDER BY ds.date ASC;
    `;

    const statement = sql`
      WITH date_series AS (
        SELECT generate_series(
          NOW() - INTERVAL '${sql.raw((days - 1).toString())} DAYS', 
          NOW(), 
          '1 day'::interval
        )::date AS date
      )
      SELECT 
        ds.date,
        COALESCE(SUM(${orders.totalPrice}), 0) AS revenue
      FROM date_series ds
      LEFT JOIN ${orders} ON date_trunc('day', ${orders.createdAt}) = ds.date
      GROUP BY ds.date
      ORDER BY ds.date ASC;
    `;

Fetch data based on client's timezone.

Similar Threads

Fetch data based on client's timezone.

Similar Threads

Similar Threads

Similar Threads