PHP:带有日期间隙的报告表
我在数据库中有一个表,其中包含几天的摘要。有些日子可能没有这些值。 我需要显示包含结果的表格,其中每列都是用户选择范围内的一天。 我尝试使用时间戳(end_date - start_date / 86400 - 报告中有多少天,然后使用 DATEDIFF(row_date, 'user_entered_start_date') 并从该索引创建数组),但现在我已经有了一大堆解决方法夏季时间:( 有任何例子或想法如何使其正确吗?
PS 我需要在 PHP 端执行此操作,因为数据库负载很高。
I have a table in DB which contains summaries for days. Some days may not have the values.
I need to display table with results where each column is a day from user selected range.
I've tried to play with timestamp (end_date - start_date / 86400 - how many days in report, then use DATEDIFF(row_date, 'user_entered_start_date') and create array from this indexes), but now I've got whole bunch of workarounds for summer time :( Any examples or ideas how to make this correct?
P.S. I need to do this on PHP side, because DB is highly loaded.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
尝试 DateTime 对象:
ETA 另一个选项:
根据您的评论,动态生成缺失的日期可能更容易。为此,您需要一个整数表、应出现在报告输出中的日期数、开始日期和日期增量。
在您的数据库中创建一个名为
numbers
的表并插入数字 0 到 9:数字表可用于生成整数序列。例如,要获取从 1 到 20 的序列:
如果将像上面这样的序列查询左连接到常规查询,则应该能够生成真实行和空白行。例如
Try the DateTime object:
ETA another option:
Based on your comments, it may be easier to dynamically generate the missing dates. For this you'll need an integer table, the number of dates that should appear in your report output, a start date and a date increment.
In your db create a table called
numbers
and insert the numbers 0 through 9:The numbers table can be used for making sequences of integers. For instance, to get a sequence from 1 to 20:
If you left join a sequence query like the above to your regular query, you should be able to generate both real and blank rows. e.g.
您可以使用空白值“预加载”数据库。然后执行 UPDATE 查询而不是插入。您所有没有数据的日子都将被预先填充。您可以创建一个简单的脚本来创建一个月/一年/十年的(空白)数据,并根据需要经常运行它。这样您就不必担心没有数据的日子如何进入数据库 - 您可以从数据“清零”开始。
You could "pre-load" the database with blank values. Then do UPDATE queries instead of inserts. All your days with no data will be pre-populated. You can create a simple script that creates a month's/year's/decade's worth of (blank) data and run it as often as you need. Then you never have to worry about how the days with no data get into the database - you start with your data "zeroed" out.