I want to let users specify a date that may or may not include a day and month (but will have at least the year.) The problem is when it is stored as a datetime in the DB; the missing day/month will be saved as default values and I'll lose the original format and meaning of the date.
我想让用户指定一个日期,该日期可能包括也可能不包括日期和月份(但至少会包含年份。)问题在于它是作为日期时间存储在数据库中的;缺失的日期/月将保存为默认值,我将丢失原始格式和日期的含义。
My idea was to store the real format in a column as a string in addition to the datetime column. Then I could use the string column whenever I have to display the date and the datetime for everything else. The downside is an extra column for every date column in the table I want to display, and printing localized dates won't be as easy since I can't rely on the datetime value... I'll probably have to parse the string.
我的想法是除了datetime列之外还将实际格式作为字符串存储在列中。然后,每当我必须显示其他所有内容的日期和日期时间时,我都可以使用字符串列。缺点是我要显示的表中每个日期列的额外列,打印本地化日期不会那么容易,因为我不能依赖日期时间值......我可能要解析字符串。
I'm hoping I've overlooked something and there might be an easier way.
我希望我忽略了一些东西,可能会有一种更简单的方法。
(Note I'm using Rails if it matters for a solution.)
(注意我使用Rails,如果它对解决方案很重要。)
11 个解决方案
#1
9
As proposed by Jhenzie, create a bitmask to show which parts of the date have been specified. 1 = Year, 2 = Month, 4 = Day, 8 = Hour (if you decide to get more specific) and then store that into another field.
正如Jhenzie所提出的,创建一个位掩码来显示指定日期的哪些部分。 1 =年,2 =月,4 =日,8 =小时(如果您决定更具体),然后将其存储到另一个字段中。
The only way that I could think of doing it without requiring extra columns in your table would be to use jhenzie's method of using a bitmask, and then store that bitmask into the seconds part of your datetime column.
我想到的唯一方法就是使用jhenzie使用位掩码的方法,然后将该位掩码存储到datetime列的秒部分。
#2
3
in your model only pay attention to the parts you care about. So you can store the entire date in your db, but you coalesce it before displaying it to the user.
在你的模型中只关注你关心的部分。因此,您可以将整个日期存储在数据库中,但在将其显示给用户之前将其合并。
#3
2
The additional column could simple be used for specifying what part of the date time has been specified
附加列可以简单地用于指定指定日期时间的哪个部分
1 = day 2 = month 4 = year
1 =第2天=第4个月=年
so 3 is day and month, 6 is month and year, 7 is all three. its a simple int at that point
所以3是日和月,6是月和年,7是3。那时它是一个简单的int
#4
2
If you store a string, don't partially reinvent ISO 8601 standard which covers the case you describe and more:
如果您存储字符串,请不要部分重新发明涵盖您描述的案例的ISO 8601标准以及更多:
#5
0
Is it really necessary to store it as a datetime at all ? If not stored it as a string 2008 or 2008-8 or 2008-8-1 - split the string on hyphens when you pull it out and you're able to establish how specific the original input was
是否真的有必要将其存储为日期时间?如果没有将它存储为字符串2008或2008-8或2008-8-1 - 当您将其拉出时将字符串拆分为连字符,并且您能够确定原始输入的具体程度
#6
0
I'd probably store the datetime and an additional "precision" column to determine how to output it. For output, the precision column can map to a column that contains the corresponding formatting string ("YYYY-mm", etc) or it can contain the formatting string itself.
我可能会存储日期时间和一个额外的“精确”列来确定如何输出它。对于输出,精度列可以映射到包含相应格式字符串(“YYYY-mm”等)的列,或者它可以包含格式化字符串本身。
#7
0
I don't know a lot about DB design, but I think a clean way to do it would be with boolean columns indicating if the user has input month and day (one column for each). Then, to save the given date, you would:
我不太了解数据库设计,但我认为一个干净的方法是使用布尔列来指示用户是否有输入月和日(每列一列)。然后,为了保存给定日期,您将:
- Store the date that the user input in a datetime column;
- Set the boolean month column if the user has picked a month;
- Set the boolean day column if the user has picked a day.
将用户输入的日期存储在datetime列中;
如果用户选择了一个月,则设置布尔月份列;
如果用户选择了一天,则设置布尔日列。
This way you know which parts of the datetime you can trust (i.e. what was input by the user).
这样您就可以知道可以信任的日期时间的哪些部分(即用户输入的内容)。
Edit: it also would be much easier to understand than having an int field with cryptic values!
编辑:它比使用具有神秘值的int字段更容易理解!
#8
0
The informix database has this facility. When you define a date field you also specify a mask of the desired time & date attributes. Only these fields count when doing comparisons.
informix数据库具有此功能。定义日期字段时,还要指定所需时间和日期属性的掩码。进行比较时只计算这些字段。
#9
0
With varying levels of specificity, your best bet is to store them as simple nullable ints. Year, Month, Day. You can encapsulate the display logic in your presentation model or a Value Object in your domain.
具有不同的特异性水平,您最好的选择是将它们存储为简单的可为空的整数。年月日。您可以将显示逻辑封装在演示模型中或域中的值对象中。
#10
0
Built-in time types represent an instant in time. You can use the built in types and create a column for precision (Year, Month, Day, Hour, Etc.) or you can create your own date structure and use nulls (or another invalid value) for empty portions.
内置时间类型代表即时。您可以使用内置类型并创建精度列(年,月,日,小时等),也可以创建自己的日期结构,并为空部分使用空值(或其他无效值)。
#11
0
For ruby at least - you could use this gem - partial-date https://github.com/58bits/partial-date
对于红宝石至少 - 你可以使用这个宝石 - 部分日期https://github.com/58bits/partial-date
#1
9
As proposed by Jhenzie, create a bitmask to show which parts of the date have been specified. 1 = Year, 2 = Month, 4 = Day, 8 = Hour (if you decide to get more specific) and then store that into another field.
正如Jhenzie所提出的,创建一个位掩码来显示指定日期的哪些部分。 1 =年,2 =月,4 =日,8 =小时(如果您决定更具体),然后将其存储到另一个字段中。
The only way that I could think of doing it without requiring extra columns in your table would be to use jhenzie's method of using a bitmask, and then store that bitmask into the seconds part of your datetime column.
我想到的唯一方法就是使用jhenzie使用位掩码的方法,然后将该位掩码存储到datetime列的秒部分。
#2
3
in your model only pay attention to the parts you care about. So you can store the entire date in your db, but you coalesce it before displaying it to the user.
在你的模型中只关注你关心的部分。因此,您可以将整个日期存储在数据库中,但在将其显示给用户之前将其合并。
#3
2
The additional column could simple be used for specifying what part of the date time has been specified
附加列可以简单地用于指定指定日期时间的哪个部分
1 = day 2 = month 4 = year
1 =第2天=第4个月=年
so 3 is day and month, 6 is month and year, 7 is all three. its a simple int at that point
所以3是日和月,6是月和年,7是3。那时它是一个简单的int
#4
2
If you store a string, don't partially reinvent ISO 8601 standard which covers the case you describe and more:
如果您存储字符串,请不要部分重新发明涵盖您描述的案例的ISO 8601标准以及更多:
#5
0
Is it really necessary to store it as a datetime at all ? If not stored it as a string 2008 or 2008-8 or 2008-8-1 - split the string on hyphens when you pull it out and you're able to establish how specific the original input was
是否真的有必要将其存储为日期时间?如果没有将它存储为字符串2008或2008-8或2008-8-1 - 当您将其拉出时将字符串拆分为连字符,并且您能够确定原始输入的具体程度
#6
0
I'd probably store the datetime and an additional "precision" column to determine how to output it. For output, the precision column can map to a column that contains the corresponding formatting string ("YYYY-mm", etc) or it can contain the formatting string itself.
我可能会存储日期时间和一个额外的“精确”列来确定如何输出它。对于输出,精度列可以映射到包含相应格式字符串(“YYYY-mm”等)的列,或者它可以包含格式化字符串本身。
#7
0
I don't know a lot about DB design, but I think a clean way to do it would be with boolean columns indicating if the user has input month and day (one column for each). Then, to save the given date, you would:
我不太了解数据库设计,但我认为一个干净的方法是使用布尔列来指示用户是否有输入月和日(每列一列)。然后,为了保存给定日期,您将:
- Store the date that the user input in a datetime column;
- Set the boolean month column if the user has picked a month;
- Set the boolean day column if the user has picked a day.
将用户输入的日期存储在datetime列中;
如果用户选择了一个月,则设置布尔月份列;
如果用户选择了一天,则设置布尔日列。
This way you know which parts of the datetime you can trust (i.e. what was input by the user).
这样您就可以知道可以信任的日期时间的哪些部分(即用户输入的内容)。
Edit: it also would be much easier to understand than having an int field with cryptic values!
编辑:它比使用具有神秘值的int字段更容易理解!
#8
0
The informix database has this facility. When you define a date field you also specify a mask of the desired time & date attributes. Only these fields count when doing comparisons.
informix数据库具有此功能。定义日期字段时,还要指定所需时间和日期属性的掩码。进行比较时只计算这些字段。
#9
0
With varying levels of specificity, your best bet is to store them as simple nullable ints. Year, Month, Day. You can encapsulate the display logic in your presentation model or a Value Object in your domain.
具有不同的特异性水平,您最好的选择是将它们存储为简单的可为空的整数。年月日。您可以将显示逻辑封装在演示模型中或域中的值对象中。
#10
0
Built-in time types represent an instant in time. You can use the built in types and create a column for precision (Year, Month, Day, Hour, Etc.) or you can create your own date structure and use nulls (or another invalid value) for empty portions.
内置时间类型代表即时。您可以使用内置类型并创建精度列(年,月,日,小时等),也可以创建自己的日期结构,并为空部分使用空值(或其他无效值)。
#11
0
For ruby at least - you could use this gem - partial-date https://github.com/58bits/partial-date
对于红宝石至少 - 你可以使用这个宝石 - 部分日期https://github.com/58bits/partial-date