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

Question

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,
  })

Mohamed El-Refaie · Accepted Answer · 2022-09-14 23:38:16Z

1

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,
  })

answered Sep 14, 2022 at 23:38

Mohamed El-Refaie

8658 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

L_J · Accepted Answer · 2024-06-17 08:59:06Z

0

For sequelize raw query mysql you should use

moment(your_time).format('YYYY-MM-DDTHH:mm:ssZ');

This will clear the problem.

edited Jun 17, 2024 at 8:59

L_J

2,45711 gold badges28 silver badges33 bronze badges

answered Jun 14, 2024 at 0:14

Petro Jackson

1

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related