数据存储是否适合储存小时班？ [英] Is datastore good for storing hr shifts?
问题描述
我想知道Google数据存储是否是我的最佳选择。
我需要存储员工的时间表，如： p>
id
name
schedule
时间表如下：
星期一上午10点至晚上10点（简单）
星期二上午10点至下午5点，下午5点30分至8点（多个，甚至不小时）
..
Sun 6 pm-4am（开始/结束在不同的日子）
如果员工现在正在工作，其中一个API返回
我试图玩与数据存储但GQL查询似乎非常有限，例如，您不能在一个查询中比较两个不同的属性（如
.. WHERE current_time> start_time AND current_time< close_time
您不能使用不等于（等于）等于
同一查询中的不同属性名称。
这意味着我需要将大量的实体加载到我的支持和解析他们浪费时间和资源，而不是从数据库获取结果
- 有没有办法使用数据存储作为我的任务？或者更好的去sql？
- 如何设计我的数据库以在数据/ sql中存储日程表？
提前谢谢
根据你的例子，我将假定工作班在30分钟增量。如果不是这样，您可以轻松调整以下内容，尽管您可能需要考虑一些优化。
以下解决方案将为您提供不断的员工时间查找，不管他们工作多少班。它也无缝地处理从一天到下一天的班次。
0。您的实体模型
我将使用以下吸引人的实体
实体种类：员工>
- id：自动编号
- 名称：字符串
- 调度：重复整数，索引
2。休息日到30分钟
24小时给你48个周期的30分钟，所以让我们将整数映射到时间段：
- 0 = 12:00 AM至12:30 AM
- 30 =上午12:30至上午1:00
- 60 = 1:00 AM至1:30 AM
- ...
- 1380 = 11:00 PM至11： 30 PM
- 1440 = 11:30 PM至12:00 AM
此整数为从你当前的时间很容易得到。使用 java.util.Calendar 和 java.util.Date ：
Date date = new Date（）; //初始化到现在
日历calendar = GregorianCalendar.getInstance（）;
calendar.setTime（date）;
int hour = calendar.get（Calendar.HOUR_OF_DAY）; // 24小时格式
int minute = calendar.get（Calendar.MINUTE）; //分钟小时
int period =小时* 60 +分钟/ 30 * 30; //整数分割舍入到最接近的30
我在compilejava.net上测试了这个测试代码： https://gist.github.com/55a98bbdb9b5eb3eeaee5f8984f11687
3。帐户星期几
现在我们在一天中有30分钟的周期块，我们应该在星期几（星期日= 0，星期六= 6）合并。由于最长30分钟的周期值是1440，所以方便的一周中的几天只有10000，所以我们可以将它们加在一起而不会有冲突：
int day = calendar.get（Calendar.DAY_OF_WEEK）;
int day_period = day * 10000 + period;
这里扩展测试代码： https://gist.github.com/217221e03b3eb143b4be45bf3f641d25
4。存储日程表
现在，您可以使用上述相同的想法将每个30分钟的时间段存储在员工计划中。在你的例子中，你有：
星期一上午10点到晚上10点（简单）
星期二上午10点到下午5点，下午5点半到8点（多，甚至不小时）
..
太阳6 pm-4am（开始/结束在不同的日子）
在计划字段（重复整数）中，这将如下所示：
10600,10630 ，10660,10690,10720,10750,10780,10810,10840,10870,10900,10930,10960,10990,11020，
11050,11080,11110,11140,11170,11200,11230,11260,11290,20600 ，20630,20660,20690,20720,20750，
20780,20810,20840,20870,20900,20930,20960,20990,21050,21080,21110,21140,21170,1080,1110，
1140 ，1170,1200,1230,1260,1290,1320,1350,1380,1410,0,30,60,90,120,150,180,210
5。查询时间！
现在查询非常简单，只是一个平等。这将给您O（1）每个员工实体的绩效，或更一般的O（n），其中 n 是现在工作的员工数量到总雇员）。
... WHERE schedule = day_period
everyone. I'm trying to figure out if Google Datastore is the best option in my case..
I need to store employees with theirs schedules like:
id
name
schedule
Schedule looks like:
Mon 10am-10pm (simple)
Tue 10am-5pm, 5.30pm-8pm (multiple, not even hours)
..
Sun 6pm-4am (start/end are in different days)
one of the APIs returns if employee is working NOW or not
I tried to play with datastore but GQL queries appeared to be very limited, for example you cant compare in one query two different properties (like
.. WHERE current_time>start_time AND current_time<close_time
You cannot use inequality operators (less than, more than, etc.) on different property names in the same query.
It means that I need to load lots of entities into my backed and parse them, wasting time and resources.. instead of getting results right from database
- Is there any way to use datastore for my task? or its better to go sql?
- how to design my database to store schedules in datasore/sql?
thanks in advance!
Based on your examples, I'm going to assume work shifts are in 30 min increments. If that is not the case you can adjust the below easily, although you may want to consider some optimizations.
The below solution will give you constant time look-ups for employees, regardless of how many shifts they work. It also seamlessly handles shifts that go from one day to the next.
0. Your entity model
I'm going to use the following straw man entity
Entity Kind: Employee
- id: Auto id
- name: string
- schedule: repeated integer, indexed
2. Break day into 30 minute blocks
24 hours gives you 48 periods of 30 minutes, so let's map integers to time periods:
- 0 = 12:00 AM to 12:30 AM
- 30 = 12:30 AM to 1:00 AM
- 60 = 1:00 AM to 1:30 AM
- ...
- 1380 = 11:00 PM to 11:30 PM
- 1440 = 11:30 PM to 12:00 AM
This integer is easy to derive from your current time. Using java.util.Calendar and java.util.Date:
Date date = new Date(); // Initializes to now.
Calendar calendar = GregorianCalendar.getInstance();
calendar.setTime(date);
int hour = calendar.get(Calendar.HOUR_OF_DAY); // Hour in 24h format
int minute = calendar.get(Calendar.MINUTE); // Minute of the hour
int period = hour*60 + minute/30*30; // Integer division rounds down to nearest 30
I tested this on compilejava.net, test code here: https://gist.github.com/55a98bbdb9b5eb3eeaee5f8984f11687
3. Account for day of week
Now we have 30 minute period blocks in the day, we should merge in the day of the week (Sunday = 0, Saturday = 6). Since the max 30 min period value is 1440, it is convenient to just multiple the day of the week by 10000 so we can add them together without conflict:
int day = calendar.get(Calendar.DAY_OF_WEEK);
int day_period = day*10000 + period;
Expanded test code here: https://gist.github.com/217221e03b3eb143b4be45bf3f641d25
4. Storing the Schedule
Now, instead of storing your schedule as start and stop times per day, use the same idea above to store each 30 minute period that an Employee is scheduled for. In your example you had:
Mon 10am-10pm (simple)
Tue 10am-5pm, 5.30pm-8pm (multiple, not even hours)
..
Sun 6pm-4am (start/end are in different days)
In the schedule field (repeated integer), this would look like:
10600,10630,10660,10690,10720,10750,10780,10810,10840,10870,10900,10930,10960,10990,11020,
11050,11080,11110,11140,11170,11200,11230,11260,11290,20600,20630,20660,20690,20720,20750,
20780,20810,20840,20870,20900,20930,20960,20990,21050,21080,21110,21140,21170,1080,1110,
1140,1170,1200,1230,1260,1290,1320,1350,1380,1410,0,30,60,90,120,150,180,210
5. Query time!
Querying is extremely easy now and merely an equality. This will give you O(1) performance per Employee entity working right now, or more generally O(n) where n is the number of employees that are working now (as opposed to total employees).
... WHERE schedule=day_period
这篇关于数据存储是否适合储存小时班？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
