Drizzle Team•6mo ago

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 type, with +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.

2 Replies

rphlmr ⚡•6mo ago

Hum good question. Not sure about that but maybe replace all NOW() with (SELECT now() AT TIME ZONE 'Europe/Paris') (if you can get the user timezone)

    const userNow = sql`(SELECT now() AT TIME ZONE 'Europe/Paris')`
    const statement = sql`
      WITH date_series AS (
        SELECT generate_series(
          ${userNow} - INTERVAL '${sql.raw((days - 1).toString())} DAYS', 
          ${userNow}, 
          '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 userNow = sql`(SELECT now() AT TIME ZONE 'Europe/Paris')`
    const statement = sql`
      WITH date_series AS (
        SELECT generate_series(
          ${userNow} - INTERVAL '${sql.raw((days - 1).toString())} DAYS', 
          ${userNow}, 
          '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;
    `;

RorschOP•5mo ago

interesting approach. yes, i think i can do that for now. thank you for giving me some insights!

Gaming

Programming

Fetch data based on client's timezone.