Get List of Users for a Specific Local Time

asanas 0
Viewed 48

In my database, I have a list of users with the user ids and their UTC offset in minutes. It's in a PostgreSQL database which I access through sequelize in my node application.

For example:

ID, offset
1, -540 
2, -330 
...
...

I want to create a query that, when run, gets me a list of users that have passed 12pm noon their local time.

For example, when UTC time is 7am and we run the query, then for user Id 2, the local time is 12:30pm, and for userId 1, the local time is 4pm. So, the query will get me [1,2]

However, when UTC time is 5am and we run the query, then for user 1, the local time is 1pm, but for user 2, the local time is 10:30am. So the query result will be [1].

I'm confused about how to structure my code to make this happen. I don't need the syntax. I just need the logic for how I should form my query or my sequelize code.

Any help is appreciated.

timezone sql javascript
2 Answers

The way to compute if the local time is after noon is to convert the UTC timestamp (in seconds) to local time by adding the timezone offset (in minutes) * 60, then taking the result modulo 86400 (seconds in a day) and testing to see if it is greater than or equal to 43200` (12:00) i.e.

afternoon = ((now()-offset*60) % 86400) >= 43200

This can be refactored in terms of the timezone offset by computing

const n = new Date()
const t = n.getUTCHours() * 60 + n.getUTCMinutes()
const afternoon = offset > t || offset <= t - 720 && offset > t - 1440
usergAKD0

You have to get the standard.

For example, when UTC time is 7am, the standard will be -300

=> So, your query will be like

SELECT * FROM ${table} WHERE offset <= ${standard} (In this case, standard is -300)

If when UTC time is 1pm, the standard will be 600, -660

=> The query will be

SELECT * FROM ${table} WHERE -660 <= offset <= 600
Hanum0
Related