How to fetch data in a range of dates based on a timezone passed using sequelize-CodePudding

I am trying to fetch data where the createdAt field falls between a date range with a timezone specified. This is what I have so far but this is not returning the correct data in the specified date range and timezone. An example of what a createdAt value in the database looks like is

2022-06-14 02:37:16.236 00

let timezone = req.header('Timezone')
if (!timezone || !moment.tz.zone(timezone)) {
    timezone = moment.tz.guess();
}

const startOfDay = moment(date).tz(timezone).startOf('day')
const endOfDay = moment(startOfDay).endOf('day')

// Fetch all completed events within the date range
const events = await models.Event.findAll({
    attributes: [
      'id',
      'accuracy',
    ],
    where: {
      status: 'completed',
      createdAt: {
        [Op.between]: [startOfDay, endOfDay],
      },
    },
    order: [['createdAt', 'ASC']],
    raw: true,
  })

CodePudding user response：

You have to format moment dates to any acceptable date and time with the timezone format of PostgreSQL. For example,

startOfDay.format("YYYY-MM-DD HH:mm:ss z")

So the code should be like this:

let timezone = req.header('Timezone')
if (!timezone || !moment.tz.zone(timezone)) {
    timezone = moment.tz.guess();
}

const startOfDay = moment(date).tz(timezone).startOf('day').format("YYYY-MM-DD HH:mm:ss z")
const endOfDay = moment(startOfDay).endOf('day').format("YYYY-MM-DD HH:mm:ss z")

// Fetch all completed events within the date range
const events = await models.Event.findAll({
    attributes: [
      'id',
      'accuracy',
    ],
    where: {
      status: 'completed',
      createdAt: {
        [Op.between]: [startOfDay, endOfDay],
      },
    },
    order: [['createdAt', 'ASC']],
    raw: true,
  })