Skip to content
Advertisement

How to Query According to Different Time Zones?

I want to show the data I have stored in Firebase Realtime Database by filtering according to the date of the users. My data in the database always works according to GMT+3 timezone. But if my user’s time is different from GMT+3, the query will not work correctly. I need some information and code for this. Does anyone have any idea how I can do it?

I store my data as unixtime in the database. For example: 1621717200

startData = FirebaseDatabase.getInstance().getReference()
                .child("totals")
                .child(moTracks);
        PeriodHow = startData.orderByChild("availableDate").startAt(1621717200).endAt(1621803599);
        PeriodHow.addListenerForSingleValueEvent(new ValueEventListener() {
}
}

Regards.

Advertisement

Answer

tl;dr

LocalDate
.of( 2021 , Month.SEPTEMBER , 26 )
.atStartOfDay( 
    ZoneId.of( "Africa/Tunis" )
)
.toInstant()
.getEpochSecond()

To complete your query, do the same but add a day by calling LocalDate#plusDays( 1 ) for the first moment of the next day.

Details

I do not know Firebase. But I can show you how to get a count of seconds since epoch reference.

Of the couple dozen commonly used epoch references, I assume your Firebase tooling is using the first moment of 1970 as seen in UTC (an offset of zero hours-minutes-seconds).

Get the current moment as a count of whole seconds since 1970-01-01T00:00Z. We use Instant to represent a moment as seen in UTC.

long secondsSinceEpoch = Instant.now().getEpochSecond() ;

Apparently you want to represent an entire day using counts of whole seconds since epoch, but track the day as seen in a particular time zone. Be aware that for any given moment, the date varies around the globe by time zone. It can be “tomorrow” in Tokyo Japan while still “yesterday” in Toledo Ohio US.

ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;

You may choose to use the JVM’s current default time zone.

ZoneId z = ZoneId.systemDefault() ;

And furthermore, politicians frequently alter the rules of a time zone, creating anomalies such as days that run longer or shorter than 24 hours, and days that do not start at 00:00. So we let java.time determine the first moment of the day by calling LocalDate#atStartOfDay.

LocalDate ld = LocalDate.of( 2021 , Month.SEPTEMBER , 26 ) ;
ZonedDateTime zdtStart = ld.atStartOfDay( z ) ;

Adjust that moment from the specific time zone to UTC by extracting a Instant object from our ZonedDateTime object. We have the same moment, the same point on the timeline, but we perceive a different wall-clock time.

Instant instantStart = zdtStart.toInstant() ;

From the Instant we get a count of whole seconds since the epoch reference. Beware of data loss: any fractional second on the Instant is ignored, of course.

long start = instantStart.getEpochSecond() ;

For the beginning of the day, we use the Half-Open approach where the beginning of the day is inclusive while the ending exclusive. So a day starts with the first moment of the day and runs up to, but does not include, the first moment of the next day. This avoids the problem of determining the infinitely divisible last moment.

LocalDate nextDay = ld.plusDays( 1 ) ;
ZonedDateTime zdtEnd = nextDay.atStartOfDay( z ) ;

Calculate time elapsed, the length of a day on that date in that zone.

Duration d = Duration.between( zdtStart , zdtEnd ) ;  // 23 hours, not 24, for Daylight Saving Time (DST) cut-over on that date in that zone.

Adjust to UTC (an offset of zero). Extract a count of whole seconds since epoch reference.

Instant instantEnd = zdtEnd.toInstant() ;
long end = instantEnd.getEpochSecond() ;

Now you can perform your query using start and end. Be sure your query is framed as Half-Open. Do not use the SQL command BETWEEN. Criteria should be using logic of “not before the start AND before the end”.

SELECT *
FROM some_table_
WHERE when_ !< ?
AND when_ < ?
;

… while using start and end longs as the values to plug into those placeholders.

By the way, if your database offers date-time data types, your should be using those as the type of your table columns rather than using mere integers.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.

Where to obtain the java.time classes?

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement