I want to store times in a database table but only need to store the hours and minutes. I know I could just use DATETIME and ignore the other components of the date, but what's the best way to do this without storing more info than I actually need?
我希望将时间存储在数据库表中，但只需要存储时间和分钟。我知道我可以使用DATETIME并忽略日期的其他组件，但是如果不存储超出实际需要的信息，最好的方法是什么?
15 个解决方案
#1
105
You could store it as an integer of the number of minutes past midnight:
您可以将其存储为午夜后分钟数的整数:
eg.
如。
0 = 00:00
60 = 01:00
252 = 04:12
You would however need to write some code to reconstitute the time, but that shouldn't be tricky.
但是，您需要编写一些代码来重新设置时间，但这并不需要技巧。
#2
38
If you are using SQL Server 2008+, consider the TIME datatype. SQLTeam article with more usage examples.
如果您正在使用SQL Server 2008+，请考虑时间数据类型。带更多使用示例的SQLTeam文章。
#3
10
Just store a regular datetime and ignore everything else. Why spend extra time writing code that loads an int, manipulates it, and converts it into a datetime, when you could just load a datetime?
只需要存储一个常规的datetime并忽略其他的一切。为什么要花费额外的时间来编写加载int、操作它并将它转换为datetime的代码，而您只需要加载datetime呢?
#4
8
DATETIME start DATETIME end
DATETIME开始日期时间结束
I implore you to use two DATETIME values instead, labelled something like event_start and event_end.
我恳求您使用两个DATETIME值，标记为event_start和event_end。
Time is a complex business
时间是一件复杂的事情
Most of the world has now adopted the denery based metric system for most measurements, rightly or wrongly. This is good overall, because at least we can all agree that a g, is a ml, is a cubic cm. At least approximately so. The metric system has many flaws, but at least it's internationally consistently flawed.
世界上大多数国家现在都采用了基于精密测量的公制，无论正确与否。这是很好的整体，因为至少我们都同意g，是一个ml，是一个立方厘米。至少约。公制有许多缺陷，但至少在国际上一直存在缺陷。
With time however, we have; 1000 milliseconds in a second, 60 seconds to a minute, 60 minutes to an hour, 12 hours for each half a day, approximately 30 days per month which vary by the month and even year in question, each country has its time offset from others, the way time is formatted in each country vary.
然而，随着时间的推移，我们已经;1000毫秒,60秒到一分钟,60分钟到一个小时,12小时对于每个半天,大约每月30天月甚至年不同的问题,每个国家都有它的时间抵消别人的,时间是格式化的方式在每个国家是不同的。
It's a lot to digest, but the long and short of it is impossible for such a complex scenario to have a simple solution.
要消化的东西很多，但如此复杂的场景不可能有一个简单的解决方案。
Some corners can be cut, but there are those where it is wiser not to
有些角落可以被剪掉，但有些地方是明智的。
Although the top answer here suggests that you store an integer of minutes past midnight might seem perfectly reasonable, I have learned to avoid doing so the hard way.
虽然上面的答案建议你在午夜前储存一个整数，这似乎是完全合理的，但我已经学会了避免这样做。
The reasons to implement two DATETIME values are for an increase in accuracy, resolution and feedback.
实现两个DATETIME值的原因是增加了精确度、分辨率和反馈。
These are all very handy for when the design produces undesirable results.
当设计产生不良结果时，这些都非常方便。
Am I storing more data than required?
我存储的数据是否超出了要求?
It might initially appear like more information is being stored than I require, but there is a good reason to take this hit.
最初看起来似乎存储的信息比我所需要的要多，但是有一个很好的理由去接受这个打击。
Storing this extra information almost always ends up saving me time and effort in the long-run, because I inevitably find that when somebody is told how long something took, they'll additionally want to know when and where the event took place too.
从长远来看，储存这些额外的信息几乎总能节省我的时间和精力，因为我不可避免地发现，当有人被告知某件事花了多长时间时，他们还会想知道这件事发生的时间和地点。
It's a huge planet
这是一个巨大的星球
In the past, I have been guilty of ignoring that there are other countries on this planet aside from my own. It seemed like a good idea at the time, but this has ALWAYS resulted in problems, headaches and wasted time later on down the line. ALWAYS consider all time zones.
在过去，我一直因为忽视地球上除了我自己以外还有其他国家而感到内疚。这在当时似乎是一个好主意，但这总是会导致问题、头痛和以后浪费时间。总是考虑所有时区。
C#
c#
A DateTime renders nicely to a string in C#. The ToString(string Format) method is compact and easy to read.
DateTime很好地呈现为c#中的字符串。ToString(字符串格式)方法紧凑，易于阅读。
E.g.
如。
new TimeSpan(EventStart.Ticks - EventEnd.Ticks).ToString("h'h 'm'm 's's'")
SQL server
SQL server
Also if you're reading your database seperate to your application interface, then dateTimes are pleasnat to read at a glance and performing calculations on them are straightforward.
此外，如果您正在将数据库分离到应用程序接口中，那么请立即阅读dateTimes并对其进行计算。
E.g.
如。
SELECT DATEDIFF(MINUTE, event_start, event_end)
ISO8601 date standard
ISO8601日期标准
If using SQLite then you don't have this, so instead use a Text field and store it in ISO8601 format eg.
如果使用SQLite，那么您没有这个，因此使用文本字段并将其存储为ISO8601格式eg。
"2013-01-27T12:30:00+0000"
“2013 - 01 - 27 t12:30:00 + 0000”
Notes:
注:
-
This uses 24 hour clock*
它使用24小时的时钟*
-
The +0000 part of the ISO8601 maps directly to lattitude in a GPS coordiate. It is therefore worth considering if it would be worth storing longitude, lattitude and altitude along with the data. This will vary in application.
ISO8601的+0000部分直接映射到GPS coordiate中。因此，值得考虑的是，它是否值得与数据一起存储经度、延迟和高度。这在应用上会有所不同。
-
ISO8601 is an international format.
ISO8601是一种国际格式。
-
The wiki is very good for further details at http://en.wikipedia.org/wiki/ISO_8601.
在http://en.wikipedia.org/wiki/ISO_8601上，wiki非常适合了解更多细节。
-
The date and time is stored in international time and the offset is recorded depending on where in the world the time was stored.
日期和时间存储在国际时间中，偏移量根据存储时间的世界位置而记录。
In my experience there is always a need to store the full date and time, regardless of whether I think there is when I begin the project. ISO8601 is a very good, futureproof way of doing it.
根据我的经验，总是需要保存完整的日期和时间，不管我是否认为在我开始项目的时候。ISO8601是一种非常好的、未来的方法。
Additional advice for free
额外的免费建议
It is also worth grouping events together like a chain. E.g. if recording a race, the whole event could be grouped by racer, race_circuit, circuit_checkpoints and circuit_laps.
它也值得像一个链一样将事件组合在一起。例如，如果记录一场比赛，整个事件可以按照赛手、赛道、环路检查点和环路圈进行分组。
In my experience, it is also wise to identify who stored the record. Either as a seperate table populated via trigger or as an additional column within the original table.
根据我的经验，确定谁存储了记录也是明智的。可以作为通过触发器填充的分离表，也可以作为原始表中的附加列。
The more you put in, the more you get out
你投入的越多，得到的就越多
I completely understand the desire to be as economical with space as possible, but I would rarely do so at the expense of losing information.
我完全理解尽可能节省空间的愿望，但我很少以丢失信息为代价。
A rule of thumb with databases is as the title says, a database can only tell you as much as it has data for, and it can be very costly to go back through historical data, filling in gaps.
数据库的一个经验法则是，数据库只能告诉你它所拥有的数据，并且通过历史数据，填补空白的代价非常昂贵。
The solution is to get it correct first time. This is certainly easier said than done, but you should now have a deeper insight of effective database design and subsequently stand a much improved chance of getting it right the first time.
解决办法是第一次把它弄对。这当然说起来容易做起来难，但您现在应该对有效的数据库设计有了更深入的了解，并随后获得了更大的成功机会。
The better your initial design, the less costly the repairs will be later on.
你最初的设计越好，以后的维修费用就越低。
I only say all this, because if I could go back in time then it is what I'd tell myself when I got there.
我只说这些，因为如果我能回到过去，当我到达那里的时候，我就会告诉自己。
#5
3
since you didn't mention it bit if you are on SQL Server 2008 you can use the time datatype otherwise use minutes since midnight
由于您在SQL Server 2008上没有提到它，所以您可以使用时间数据类型，否则从午夜开始使用分钟。
#6
2
I would convert them to an integer (HH*3600 + MM*60), and store it that way. Small storage size, and still easy enough to work with.
我将它们转换为一个整数(HH*3600 + MM*60)，并以这种方式存储它。存储空间小，并且仍然容易使用。
#7
2
If you are using MySQL use a field type of TIME and the associated functionality that comes with TIME.
如果您正在使用MySQL，请使用字段类型的时间和随时间而来的相关功能。
00:00:00 is standard unix time format.
00:00是标准的unix时间格式。
If you ever have to look back and review the tables by hand, integers can be more confusing than an actual time stamp.
如果您需要回头查看并手工查看表，整数可能比实际的时间戳更令人困惑。
#8
2
SQL Server actually stores time as fractions of a day. For example, 1 whole day = value of 1. 12 hours is a value of 0.5.
实际上，SQL Server将时间存储为一天的一部分。例如，1全天= 1的值。12小时等于0。5。
If you want to store the time value without utilizing a DATETIME type, storing the time in a decimal form would suit that need, while also making conversion to a DATETIME simple.
如果您希望在不使用DATETIME类型的情况下存储时间值，那么将时间存储为小数形式将适合这种需要，同时还可以简化对DATETIME的转换。
For example:
例如:
SELECT CAST(0.5 AS DATETIME)
--1900-01-01 12:00:00.000
Storing the value as a DECIMAL(9,9) would consume 5 bytes. However, if precision to not of utmost importance, a REAL would consume only 4 bytes. In either case, aggregate calculation (i.e. mean time) can be easily calculated on numeric values, but not on Data/Time types.
将值存储为小数(9,9)将消耗5个字节。然而，如果精度不是最重要的，一个实数将只消耗4字节。在任何一种情况下，聚合计算(即平均时间)都可以很容易地计算数值，但不能计算数据/时间类型。
#9
1
Try smalldatetime. It may not give you what you want but it will help you in your future needs in date/time manipulations.
smalldatetime试试。它可能不会给你想要的东西，但是它会在你未来的需求中帮助你处理日期/时间。
#10
1
Are you sure you will only ever need the hours and minutes? If you want to do anything meaningful with it (like for example compute time spans between two such data points) not having information about time zones and DST may give incorrect results. Time zones do maybe not apply in your case, but DST most certainly will.
你确定你只需要时间和时间吗?如果您想用它做任何有意义的事情(比如计算两个这样的数据点之间的时间跨度)，没有关于时区的信息，DST可能会给出不正确的结果。时区可能不适用于你的情况，但DST肯定适用。
#11
1
What I think you're asking for is a variable that will store minutes as a number. This can be done with the varying types of integer variable:
我想你要求的是一个变量，它将分钟存储为一个数字。这可以用不同类型的整型变量来实现:
SELECT 9823754987598 AS MinutesInput
Then, in your program you could simply view this in the form you'd like by calculating:
然后，在你的程序中，你可以通过计算简单地以你想要的形式来看待这个问题:
long MinutesInAnHour = 60;
long MinutesInADay = MinutesInAnHour * 24;
long MinutesInAWeek = MinutesInADay * 7;
long MinutesCalc = long.Parse(rdr["MinutesInput"].toString()); //BigInt converts to long. rdr is an SqlDataReader.
long Weeks = MinutesCalc / MinutesInAWeek;
MinutesCalc -= Weeks * MinutesInAWeek;
long Days = MinutesCalc / MinutesInADay;
MinutesCalc -= Days * MinutesInADay;
long Hours = MinutesCalc / MinutesInAnHour;
MinutesCalc -= Hours * MinutesInAnHour;
long Minutes = MinutesCalc;
An issue arises where you request for efficiency to be used. But, if you're short for time then just use a nullable BigInt to store your minutes value.
当您要求使用效率时，会出现一个问题。但是，如果您的时间很短，那么只需使用一个可空的BigInt来存储分钟值。
A value of null means that the time hasn't been recorded yet.
null的值表示时间还没有被记录。
Now, I will explain in the form of a round-trip to outer-space.
现在，我将以往返于外太空的形式来解释。
Unfortunately, a table column will only store a single type. Therefore, you will need to create a new table for each type as it is required.
不幸的是，表列将只存储一个类型。因此，您需要根据需要为每个类型创建一个新表。
For example:
例如:
-
If MinutesInput = 0..255 then use TinyInt (Convert as described above).
如果MinutesInput = 0 . .然后使用TinyInt(如上所述进行转换)。
-
If MinutesInput = 256..131071 then use SmallInt (Note: SmallInt's min value is -32,768. Therefore, negate and add 32768 when storing and retrieving value to utilise full range before converting as above).
如果MinutesInput = 256 . .131071然后使用SmallInt(注意:SmallInt的最小值是-32,768。因此，在存储和检索值时，在如上所述转换之前，先进行否定并添加32768)。
-
If MinutesInput = 131072..8589934591 then use Int (Note: Negate and add 2147483648 as necessary).
如果MinutesInput = 131072 . .8589934591然后使用Int(注意:否定词，必要时添加2147483648)。
-
If MinutesInput = 8589934592..36893488147419103231 then use BigInt (Note: Add and negate 9223372036854775808 as necessary).
如果MinutesInput = 8589934592 . .然后使用BigInt(注:必要时添加和否定9223372036854775808)。
-
If MinutesInput > 36893488147419103231 then I'd personally use VARCHAR(X) increasing X as necessary since a char is a byte. I shall have to revisit this answer at a later date to describe this in full (or maybe a fellow *ee can finish this answer).
如果MinutesInput > 36893488147419103231，那么我个人会使用VARCHAR(X)增加X，因为char是一个字节。我将在稍后的时间重新讨论这个答案，以完整地描述这个问题(或者可能一个*ee的同事可以完成这个答案)。
Since each value will undoubtedly require a unique key, the efficiency of the database will only be apparent if the range of the values stored are a good mix between very small (close to 0 minutes) and very high (Greater than 8589934591).
由于每个值无疑都需要一个惟一的键，因此只有当存储的值的范围在非常小(接近0分钟)和非常高(大于8589934591)之间很好地混合时，数据库的效率才会很明显。
Until the values being stored actually reach a number greater than 36893488147419103231 then you might as well have a single BigInt column to represent your minutes, as you won't need to waste an Int on a unique identifier and another int to store the minutes value.
在被存储的值实际到达一个大于36893488147419103231的数字之前，您还可以使用一个BigInt列来表示分钟，因为您不需要在唯一标识符上浪费一个Int值，而需要使用另一个Int来存储分钟值。
#12
0
Instead of minutes-past-midnight we store it as 24 hours clock, as an SMALLINT.
我们把它当作24小时的时钟，当作一个小的整数，而不是一分钟的午夜。
09:12 = 912 14:15 = 1415
09:12 = 912 14:15 = 1415。
when converting back to "human readable form" we just insert a colon ":" two characters from the right. Left-pad with zeros if you need to. Saves the mathematics each way, and uses a few fewer bytes (compared to varchar), plus enforces that the value is numeric (rather than alphanumeric)
当转换回“人类可读形式”时，我们只需插入一个冒号“:”右边的两个字符。如果需要的话，可以用0的左半边栏。每一种方法都节省数学，并且使用更少的字节(与varchar相比)，并且强制该值为数值(而不是字母数字)
Pretty goofy though ... there should have been a TIME datatype in MS SQL for many a year already IMHO ...
虽然傻傻的……在MS SQL中应该有一个时间数据类型已经很多年了……
#13
0
The saving of time in UTC format can help better as Kristen suggested.
用UTC格式节省时间可以帮助更好，正如Kristen所建议的。
Make sure that you are using 24 hr clock because there is no meridian AM or PM be used in UTC.
确保您正在使用24小时的时钟，因为在UTC中没有子午线AM或PM。
Example:
例子:
- 4:12 AM - 0412
- 4:12 AM - 0412
- 10:12 AM - 1012
- 十12点- 1012
- 2:28 PM - 1428
- 下午2:28 - 1428
- 11:56 PM - 2356
- 下午56 - 2356
Its still preferrable to use standard four digit format.
它仍然可以使用标准的四位数格式。
#14
0
Store the ticks as a long/bigint, which are currently measured in milliseconds. The updated value can be found by looking at the TimeSpan.TicksPerSecond value.
将计时单位存储为长/大整数，当前以毫秒为单位。可以通过查看TimeSpan找到更新后的值。TicksPerSecond价值。
Most databases have a DateTime type that automatically stores the time as ticks behind the scenes, but in the case of some databases e.g. SqlLite, storing ticks can be a way to store the date.
大多数数据库都有一个DateTime类型，它可以自动地将时间作为计时单位存储在后台，但是对于某些数据库，例如SqlLite，存储计时单位可以是一种存储日期的方式。
Most languages allow the easy conversion from Ticks → TimeSpan → Ticks.
大多数语言允许简单的转换从蜱虫→时间间隔→蜱虫。
Example
例子
In C# the code would be:
在c#中，代码是:
long TimeAsTicks = TimeAsTimeSpan.Ticks;
TimeAsTimeSpan = TimeSpan.FromTicks(TimeAsTicks);
Be aware though, because in the case of SqlLite, which only offers a small number of different types, which are; INT, REAL and VARCHAR It will be necessary to store the number of ticks as a string or two INT cells combined. This is, because an INT is a 32bit signed number whereas BIGINT is a 64bit signed number.
但是要注意，因为在SqlLite的例子中，它只提供少量不同的类型;需要将滴答数作为一个字符串或两个INT单元的组合来存储。这是因为INT是一个32位有符号的数字，而BIGINT是一个64位有符号的数字。
Note
请注意
My personal preference however, would be to store the date and time as an ISO8601 string.
不过，我个人的偏好是将日期和时间存储为ISO8601字符串。
#15
-1
IMHO what the best solution is depends to some extent on how you store time in the rest of the database (and the rest of your application)
最好的解决方案是什么在某种程度上取决于如何在数据库的其他部分(以及应用程序的其他部分)存储时间
Personally I have worked with SQLite and try to always use unix timestamps for storing absolute time, so when dealing with the time of day (like you ask for) I do what Glen Solsberry writes in his answer and store the number of seconds since midnight
就我个人而言，我曾与SQLite一起工作过，并试图始终使用unix时间戳来存储绝对时间，因此，在处理一天中的时间(如您所要求的)时，我会按照Glen Solsberry在他的回答中所写的那样，并存储从午夜开始的秒数
When taking this general approach people (including me!) reading the code are less confused if I use the same standard everywhere
当使用这种通用的方法时，如果我在任何地方都使用相同的标准，阅读代码的人(包括我!)就不会那么困惑
#1
105
You could store it as an integer of the number of minutes past midnight:
您可以将其存储为午夜后分钟数的整数:
eg.
如。
0 = 00:00
60 = 01:00
252 = 04:12
You would however need to write some code to reconstitute the time, but that shouldn't be tricky.
但是，您需要编写一些代码来重新设置时间，但这并不需要技巧。
#2
38
If you are using SQL Server 2008+, consider the TIME datatype. SQLTeam article with more usage examples.
如果您正在使用SQL Server 2008+，请考虑时间数据类型。带更多使用示例的SQLTeam文章。
#3
10
Just store a regular datetime and ignore everything else. Why spend extra time writing code that loads an int, manipulates it, and converts it into a datetime, when you could just load a datetime?
只需要存储一个常规的datetime并忽略其他的一切。为什么要花费额外的时间来编写加载int、操作它并将它转换为datetime的代码，而您只需要加载datetime呢?
#4
8
DATETIME start DATETIME end
DATETIME开始日期时间结束
I implore you to use two DATETIME values instead, labelled something like event_start and event_end.
我恳求您使用两个DATETIME值，标记为event_start和event_end。
Time is a complex business
时间是一件复杂的事情
Most of the world has now adopted the denery based metric system for most measurements, rightly or wrongly. This is good overall, because at least we can all agree that a g, is a ml, is a cubic cm. At least approximately so. The metric system has many flaws, but at least it's internationally consistently flawed.
世界上大多数国家现在都采用了基于精密测量的公制，无论正确与否。这是很好的整体，因为至少我们都同意g，是一个ml，是一个立方厘米。至少约。公制有许多缺陷，但至少在国际上一直存在缺陷。
With time however, we have; 1000 milliseconds in a second, 60 seconds to a minute, 60 minutes to an hour, 12 hours for each half a day, approximately 30 days per month which vary by the month and even year in question, each country has its time offset from others, the way time is formatted in each country vary.
然而，随着时间的推移，我们已经;1000毫秒,60秒到一分钟,60分钟到一个小时,12小时对于每个半天,大约每月30天月甚至年不同的问题,每个国家都有它的时间抵消别人的,时间是格式化的方式在每个国家是不同的。
It's a lot to digest, but the long and short of it is impossible for such a complex scenario to have a simple solution.
要消化的东西很多，但如此复杂的场景不可能有一个简单的解决方案。
Some corners can be cut, but there are those where it is wiser not to
有些角落可以被剪掉，但有些地方是明智的。
Although the top answer here suggests that you store an integer of minutes past midnight might seem perfectly reasonable, I have learned to avoid doing so the hard way.
虽然上面的答案建议你在午夜前储存一个整数，这似乎是完全合理的，但我已经学会了避免这样做。
The reasons to implement two DATETIME values are for an increase in accuracy, resolution and feedback.
实现两个DATETIME值的原因是增加了精确度、分辨率和反馈。
These are all very handy for when the design produces undesirable results.
当设计产生不良结果时，这些都非常方便。
Am I storing more data than required?
我存储的数据是否超出了要求?
It might initially appear like more information is being stored than I require, but there is a good reason to take this hit.
最初看起来似乎存储的信息比我所需要的要多，但是有一个很好的理由去接受这个打击。
Storing this extra information almost always ends up saving me time and effort in the long-run, because I inevitably find that when somebody is told how long something took, they'll additionally want to know when and where the event took place too.
从长远来看，储存这些额外的信息几乎总能节省我的时间和精力，因为我不可避免地发现，当有人被告知某件事花了多长时间时，他们还会想知道这件事发生的时间和地点。
It's a huge planet
这是一个巨大的星球
In the past, I have been guilty of ignoring that there are other countries on this planet aside from my own. It seemed like a good idea at the time, but this has ALWAYS resulted in problems, headaches and wasted time later on down the line. ALWAYS consider all time zones.
在过去，我一直因为忽视地球上除了我自己以外还有其他国家而感到内疚。这在当时似乎是一个好主意，但这总是会导致问题、头痛和以后浪费时间。总是考虑所有时区。
C#
c#
A DateTime renders nicely to a string in C#. The ToString(string Format) method is compact and easy to read.
DateTime很好地呈现为c#中的字符串。ToString(字符串格式)方法紧凑，易于阅读。
E.g.
如。
new TimeSpan(EventStart.Ticks - EventEnd.Ticks).ToString("h'h 'm'm 's's'")
SQL server
SQL server
Also if you're reading your database seperate to your application interface, then dateTimes are pleasnat to read at a glance and performing calculations on them are straightforward.
此外，如果您正在将数据库分离到应用程序接口中，那么请立即阅读dateTimes并对其进行计算。
E.g.
如。
SELECT DATEDIFF(MINUTE, event_start, event_end)
ISO8601 date standard
ISO8601日期标准
If using SQLite then you don't have this, so instead use a Text field and store it in ISO8601 format eg.
如果使用SQLite，那么您没有这个，因此使用文本字段并将其存储为ISO8601格式eg。
"2013-01-27T12:30:00+0000"
“2013 - 01 - 27 t12:30:00 + 0000”
Notes:
注:
-
This uses 24 hour clock*
它使用24小时的时钟*
-
The +0000 part of the ISO8601 maps directly to lattitude in a GPS coordiate. It is therefore worth considering if it would be worth storing longitude, lattitude and altitude along with the data. This will vary in application.
ISO8601的+0000部分直接映射到GPS coordiate中。因此，值得考虑的是，它是否值得与数据一起存储经度、延迟和高度。这在应用上会有所不同。
-
ISO8601 is an international format.
ISO8601是一种国际格式。
-
The wiki is very good for further details at http://en.wikipedia.org/wiki/ISO_8601.
在http://en.wikipedia.org/wiki/ISO_8601上，wiki非常适合了解更多细节。
-
The date and time is stored in international time and the offset is recorded depending on where in the world the time was stored.
日期和时间存储在国际时间中，偏移量根据存储时间的世界位置而记录。
In my experience there is always a need to store the full date and time, regardless of whether I think there is when I begin the project. ISO8601 is a very good, futureproof way of doing it.
根据我的经验，总是需要保存完整的日期和时间，不管我是否认为在我开始项目的时候。ISO8601是一种非常好的、未来的方法。
Additional advice for free
额外的免费建议
It is also worth grouping events together like a chain. E.g. if recording a race, the whole event could be grouped by racer, race_circuit, circuit_checkpoints and circuit_laps.
它也值得像一个链一样将事件组合在一起。例如，如果记录一场比赛，整个事件可以按照赛手、赛道、环路检查点和环路圈进行分组。
In my experience, it is also wise to identify who stored the record. Either as a seperate table populated via trigger or as an additional column within the original table.
根据我的经验，确定谁存储了记录也是明智的。可以作为通过触发器填充的分离表，也可以作为原始表中的附加列。
The more you put in, the more you get out
你投入的越多，得到的就越多
I completely understand the desire to be as economical with space as possible, but I would rarely do so at the expense of losing information.
我完全理解尽可能节省空间的愿望，但我很少以丢失信息为代价。
A rule of thumb with databases is as the title says, a database can only tell you as much as it has data for, and it can be very costly to go back through historical data, filling in gaps.
数据库的一个经验法则是，数据库只能告诉你它所拥有的数据，并且通过历史数据，填补空白的代价非常昂贵。
The solution is to get it correct first time. This is certainly easier said than done, but you should now have a deeper insight of effective database design and subsequently stand a much improved chance of getting it right the first time.
解决办法是第一次把它弄对。这当然说起来容易做起来难，但您现在应该对有效的数据库设计有了更深入的了解，并随后获得了更大的成功机会。
The better your initial design, the less costly the repairs will be later on.
你最初的设计越好，以后的维修费用就越低。
I only say all this, because if I could go back in time then it is what I'd tell myself when I got there.
我只说这些，因为如果我能回到过去，当我到达那里的时候，我就会告诉自己。
#5
3
since you didn't mention it bit if you are on SQL Server 2008 you can use the time datatype otherwise use minutes since midnight
由于您在SQL Server 2008上没有提到它，所以您可以使用时间数据类型，否则从午夜开始使用分钟。
#6
2
I would convert them to an integer (HH*3600 + MM*60), and store it that way. Small storage size, and still easy enough to work with.
我将它们转换为一个整数(HH*3600 + MM*60)，并以这种方式存储它。存储空间小，并且仍然容易使用。
#7
2
If you are using MySQL use a field type of TIME and the associated functionality that comes with TIME.
如果您正在使用MySQL，请使用字段类型的时间和随时间而来的相关功能。
00:00:00 is standard unix time format.
00:00是标准的unix时间格式。
If you ever have to look back and review the tables by hand, integers can be more confusing than an actual time stamp.
如果您需要回头查看并手工查看表，整数可能比实际的时间戳更令人困惑。
#8
2
SQL Server actually stores time as fractions of a day. For example, 1 whole day = value of 1. 12 hours is a value of 0.5.
实际上，SQL Server将时间存储为一天的一部分。例如，1全天= 1的值。12小时等于0。5。
If you want to store the time value without utilizing a DATETIME type, storing the time in a decimal form would suit that need, while also making conversion to a DATETIME simple.
如果您希望在不使用DATETIME类型的情况下存储时间值，那么将时间存储为小数形式将适合这种需要，同时还可以简化对DATETIME的转换。
For example:
例如:
SELECT CAST(0.5 AS DATETIME)
--1900-01-01 12:00:00.000
Storing the value as a DECIMAL(9,9) would consume 5 bytes. However, if precision to not of utmost importance, a REAL would consume only 4 bytes. In either case, aggregate calculation (i.e. mean time) can be easily calculated on numeric values, but not on Data/Time types.
将值存储为小数(9,9)将消耗5个字节。然而，如果精度不是最重要的，一个实数将只消耗4字节。在任何一种情况下，聚合计算(即平均时间)都可以很容易地计算数值，但不能计算数据/时间类型。
#9
1
Try smalldatetime. It may not give you what you want but it will help you in your future needs in date/time manipulations.
smalldatetime试试。它可能不会给你想要的东西，但是它会在你未来的需求中帮助你处理日期/时间。
#10
1
Are you sure you will only ever need the hours and minutes? If you want to do anything meaningful with it (like for example compute time spans between two such data points) not having information about time zones and DST may give incorrect results. Time zones do maybe not apply in your case, but DST most certainly will.
你确定你只需要时间和时间吗?如果您想用它做任何有意义的事情(比如计算两个这样的数据点之间的时间跨度)，没有关于时区的信息，DST可能会给出不正确的结果。时区可能不适用于你的情况，但DST肯定适用。
#11
1
What I think you're asking for is a variable that will store minutes as a number. This can be done with the varying types of integer variable:
我想你要求的是一个变量，它将分钟存储为一个数字。这可以用不同类型的整型变量来实现:
SELECT 9823754987598 AS MinutesInput
Then, in your program you could simply view this in the form you'd like by calculating:
然后，在你的程序中，你可以通过计算简单地以你想要的形式来看待这个问题:
long MinutesInAnHour = 60;
long MinutesInADay = MinutesInAnHour * 24;
long MinutesInAWeek = MinutesInADay * 7;
long MinutesCalc = long.Parse(rdr["MinutesInput"].toString()); //BigInt converts to long. rdr is an SqlDataReader.
long Weeks = MinutesCalc / MinutesInAWeek;
MinutesCalc -= Weeks * MinutesInAWeek;
long Days = MinutesCalc / MinutesInADay;
MinutesCalc -= Days * MinutesInADay;
long Hours = MinutesCalc / MinutesInAnHour;
MinutesCalc -= Hours * MinutesInAnHour;
long Minutes = MinutesCalc;
An issue arises where you request for efficiency to be used. But, if you're short for time then just use a nullable BigInt to store your minutes value.
当您要求使用效率时，会出现一个问题。但是，如果您的时间很短，那么只需使用一个可空的BigInt来存储分钟值。
A value of null means that the time hasn't been recorded yet.
null的值表示时间还没有被记录。
Now, I will explain in the form of a round-trip to outer-space.
现在，我将以往返于外太空的形式来解释。
Unfortunately, a table column will only store a single type. Therefore, you will need to create a new table for each type as it is required.
不幸的是，表列将只存储一个类型。因此，您需要根据需要为每个类型创建一个新表。
For example:
例如:
-
If MinutesInput = 0..255 then use TinyInt (Convert as described above).
如果MinutesInput = 0 . .然后使用TinyInt(如上所述进行转换)。
-
If MinutesInput = 256..131071 then use SmallInt (Note: SmallInt's min value is -32,768. Therefore, negate and add 32768 when storing and retrieving value to utilise full range before converting as above).
如果MinutesInput = 256 . .131071然后使用SmallInt(注意:SmallInt的最小值是-32,768。因此，在存储和检索值时，在如上所述转换之前，先进行否定并添加32768)。
-
If MinutesInput = 131072..8589934591 then use Int (Note: Negate and add 2147483648 as necessary).
如果MinutesInput = 131072 . .8589934591然后使用Int(注意:否定词，必要时添加2147483648)。
-
If MinutesInput = 8589934592..36893488147419103231 then use BigInt (Note: Add and negate 9223372036854775808 as necessary).
如果MinutesInput = 8589934592 . .然后使用BigInt(注:必要时添加和否定9223372036854775808)。
-
If MinutesInput > 36893488147419103231 then I'd personally use VARCHAR(X) increasing X as necessary since a char is a byte. I shall have to revisit this answer at a later date to describe this in full (or maybe a fellow *ee can finish this answer).
如果MinutesInput > 36893488147419103231，那么我个人会使用VARCHAR(X)增加X，因为char是一个字节。我将在稍后的时间重新讨论这个答案，以完整地描述这个问题(或者可能一个*ee的同事可以完成这个答案)。
Since each value will undoubtedly require a unique key, the efficiency of the database will only be apparent if the range of the values stored are a good mix between very small (close to 0 minutes) and very high (Greater than 8589934591).
由于每个值无疑都需要一个惟一的键，因此只有当存储的值的范围在非常小(接近0分钟)和非常高(大于8589934591)之间很好地混合时，数据库的效率才会很明显。
Until the values being stored actually reach a number greater than 36893488147419103231 then you might as well have a single BigInt column to represent your minutes, as you won't need to waste an Int on a unique identifier and another int to store the minutes value.
在被存储的值实际到达一个大于36893488147419103231的数字之前，您还可以使用一个BigInt列来表示分钟，因为您不需要在唯一标识符上浪费一个Int值，而需要使用另一个Int来存储分钟值。
#12
0
Instead of minutes-past-midnight we store it as 24 hours clock, as an SMALLINT.
我们把它当作24小时的时钟，当作一个小的整数，而不是一分钟的午夜。
09:12 = 912 14:15 = 1415
09:12 = 912 14:15 = 1415。
when converting back to "human readable form" we just insert a colon ":" two characters from the right. Left-pad with zeros if you need to. Saves the mathematics each way, and uses a few fewer bytes (compared to varchar), plus enforces that the value is numeric (rather than alphanumeric)
当转换回“人类可读形式”时，我们只需插入一个冒号“:”右边的两个字符。如果需要的话，可以用0的左半边栏。每一种方法都节省数学，并且使用更少的字节(与varchar相比)，并且强制该值为数值(而不是字母数字)
Pretty goofy though ... there should have been a TIME datatype in MS SQL for many a year already IMHO ...
虽然傻傻的……在MS SQL中应该有一个时间数据类型已经很多年了……
#13
0
The saving of time in UTC format can help better as Kristen suggested.
用UTC格式节省时间可以帮助更好，正如Kristen所建议的。
Make sure that you are using 24 hr clock because there is no meridian AM or PM be used in UTC.
确保您正在使用24小时的时钟，因为在UTC中没有子午线AM或PM。
Example:
例子:
- 4:12 AM - 0412
- 4:12 AM - 0412
- 10:12 AM - 1012
- 十12点- 1012
- 2:28 PM - 1428
- 下午2:28 - 1428
- 11:56 PM - 2356
- 下午56 - 2356
Its still preferrable to use standard four digit format.
它仍然可以使用标准的四位数格式。
#14
0
Store the ticks as a long/bigint, which are currently measured in milliseconds. The updated value can be found by looking at the TimeSpan.TicksPerSecond value.
将计时单位存储为长/大整数，当前以毫秒为单位。可以通过查看TimeSpan找到更新后的值。TicksPerSecond价值。
Most databases have a DateTime type that automatically stores the time as ticks behind the scenes, but in the case of some databases e.g. SqlLite, storing ticks can be a way to store the date.
大多数数据库都有一个DateTime类型，它可以自动地将时间作为计时单位存储在后台，但是对于某些数据库，例如SqlLite，存储计时单位可以是一种存储日期的方式。
Most languages allow the easy conversion from Ticks → TimeSpan → Ticks.
大多数语言允许简单的转换从蜱虫→时间间隔→蜱虫。
Example
例子
In C# the code would be:
在c#中，代码是:
long TimeAsTicks = TimeAsTimeSpan.Ticks;
TimeAsTimeSpan = TimeSpan.FromTicks(TimeAsTicks);
Be aware though, because in the case of SqlLite, which only offers a small number of different types, which are; INT, REAL and VARCHAR It will be necessary to store the number of ticks as a string or two INT cells combined. This is, because an INT is a 32bit signed number whereas BIGINT is a 64bit signed number.
但是要注意，因为在SqlLite的例子中，它只提供少量不同的类型;需要将滴答数作为一个字符串或两个INT单元的组合来存储。这是因为INT是一个32位有符号的数字，而BIGINT是一个64位有符号的数字。
Note
请注意
My personal preference however, would be to store the date and time as an ISO8601 string.
不过，我个人的偏好是将日期和时间存储为ISO8601字符串。
#15
-1
IMHO what the best solution is depends to some extent on how you store time in the rest of the database (and the rest of your application)
最好的解决方案是什么在某种程度上取决于如何在数据库的其他部分(以及应用程序的其他部分)存储时间
Personally I have worked with SQLite and try to always use unix timestamps for storing absolute time, so when dealing with the time of day (like you ask for) I do what Glen Solsberry writes in his answer and store the number of seconds since midnight
就我个人而言，我曾与SQLite一起工作过，并试图始终使用unix时间戳来存储绝对时间，因此，在处理一天中的时间(如您所要求的)时，我会按照Glen Solsberry在他的回答中所写的那样，并存储从午夜开始的秒数
When taking this general approach people (including me!) reading the code are less confused if I use the same standard everywhere
当使用这种通用的方法时，如果我在任何地方都使用相同的标准，阅读代码的人(包括我!)就不会那么困惑