How to query correctly a date period in firestore and cloudfunctions

Question

I have a collection with the next item and one of their properties is the date:

id:   xxxxxx
name: xxxx
date: August 21, 2018 at 1:00:00 AM UTC+8 (timestamp)

Inside a firebase cloud function I am trying to query all objects from a period, this period can be the day, the week, the year, etc.

I want to query the items by the current server day, so I do this in a Firebase Cloud Function:

let auxDate = moment();
dateStart = auxDate.startOf('day').toDate();
dateEnd = auxDate.endOf('day').toDate();
await admin.firestore().collection('items')
            .where("date", ">=", dateStart)
            .where('date', '<=', dateEnd).get();

The values of dateStart and dateEnd printed in the console are:

Date start: Tue Aug 21 2018 00:00:00 GMT+0000 (UTC)
Date end:   Tue Aug 21 2018 23:59:59 GMT+0000 (UTC)

And the query return 0 items. But when I change the date of the item to

id:   xxxxxx
name: xxxx
date: August 21, 2018 at 8:00:00 AM UTC+8 (timestamp)

The query return the item correctly.

So now i know the problem is about the Offset, but how can i fix this? Why firebase save all dates in UTC+8?

Possible duplicate of [Cloud Firestore: Storing and querying for today's date over multiple UTC offsets?](https://stackoverflow.com/questions/48533178/cloud-firestore-storing-and-querying-for-todays-date-over-multiple-utc-offsets) — Martin Zeitler, Aug 21 '18 at 03:01

Yayo Arellano · Accepted Answer · 2018-08-23T02:42:51.743

I found the answer to my own question. I will explain in detail.

Understand the problem:
First thing is to remember we are doing this query inside a Cloud Function so we do not know the client timezone. In Firestore all dates are saved in UTC+00. So if i have an item like:

id:   xxxxxx
name: xxxx
date: August 22, 2018 at 12:00:00 AM UTC+8 (timestamp)

it means this date is equals to August 21, 2018 at 04:00:00 PM UTC+0.

So if i want to query all dates between:

Date start: August 22, 2018 at 12:00:00 AM UTC+8 (timestamp) 
Date end: August 22, 2018 at 11:59:59 PM UTC+8 (timestamp)

It means the query MUST be like:

Date start: August 21, 2018 at 04:00:00 AM UTC+0 (timestamp) 
Date end: August 22, 2018 at 03:59:59 PM UTC+0 (timestamp)

So the problem is when I do let auxDate = moment() this create a date with server time with no offset so the value of auxDate is for example: August 21, 2018 at 11:23:43 PM UTC+0.

So the starting dates from auxDate become:

auxDate start: August 21, 2018 at 12:00:00 AM UTC+0 (timestamp) 
auxDate end: August 21, 2018 at 11:59:59 PM UTC+0 (timestamp)

As we can see this query do not fill the ranges of our MUST be query and many items will be out.

Problem conclusion:
We cannot query correctly if we do not know the time zone of the client.

Solution:
Now i pass to this cloud function the current time of the client and calculate the start and end dates:

const clientCurrentDateTime = "2018-08-22T11:23:15+08:00";
let startDate = moment(clientCurrentDateTime).utcOffset(clientCurrentDateTime).startOf('day').toDate();
let endDate = moment(clientCurrentDateTime).utcOffset(clientCurrentDateTime).endOf('day').toDate();

This creates the correct start and end date-times for different offsets/timezones

How to query correctly a date period in firestore and cloudfunctions

1 Answers1