使用Date.commercial将Mysql星期转换为日期

Question

从数据库获取对象后

Object.select('week(created_at) as week, year(created_at) as year')

它返回从0到53的week，然后从它创建一个Date对象

Date.commercial(x.year,x.week,1)

由于第0和53周的原因，It报告“无效日期”错误。

我也试过这个。

Date.strptime("#{x.year}-#{x.week+1}-1","%Y-%W-%w")

但它也会因为x.week+1 (例如53+1)而崩溃。搜索一行解决方案

Answer 1

不要在strptime中将1添加到x.week。

Answer 2

您应该以ISO 8601格式从DB中获取日期。此格式返回从1到52的周，符合#commercial期望的输入。

在Ruby中：

ISO 8601 week-based year and week number:
The week 1 of YYYY starts with a Monday and includes YYYY-01-04.
The days in the year before the first week are in the last week of
the previous year.
  %G - The week-based year
  %g - The last 2 digits of the week-based year (00..99)
  %V - Week number of the week-based year (01..53)

来自https://ruby-doc.org/stdlib-2.5.1/libdoc/date/rdoc/Date.html#method-i-strftime

在MySQL中：

%V  Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v  Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%X  Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x  Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v


SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52                            |
+------------------------------------+

来自https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format