데이터베이스에 시간 (hh : mm)을 저장하는 가장 좋은 방법

데이터베이스에 시간 (hh : mm)을 저장하는 가장 좋은 방법

---

시간을 데이터베이스 테이블에 저장하고 싶지만 시간과 분만 저장하면됩니다. DATETIME을 사용하고 날짜의 다른 구성 요소를 무시할 수 있다는 것을 알고 있지만 실제로 필요한 것보다 더 많은 정보를 저장하지 않고이를 수행하는 가장 좋은 방법은 무엇입니까?

---

자정을 지난 분의 정수로 저장할 수 있습니다.

예.

0 = 00:00 
60 = 01:00
252 = 04:12

그러나 시간을 재구성하려면 몇 가지 코드를 작성해야하지만 까다로워서는 안됩니다.

---

SQL Server 2008+를 사용하는 경우 TIME데이터 유형을 고려하십시오 . 더 많은 사용 예제가있는 SQLTeam 기사 .

---

DATETIME 시작 DATETIME 종료

대신 event_start 및 event_end 와 같은 레이블이 붙은 두 개의 DATETIME 값 을 사용 하시기 바랍니다 .

시간은 복잡한 사업

이제 세계 대부분이 대부분의 측정에 대해 옳든 그르 든 데 너리 기반 미터법을 채택했습니다. 이것은 전반적으로 좋습니다. 왜냐하면 적어도 우리 모두는 ag가 ml이고, 입방 cm라는 것에 동의 할 수 있기 때문입니다. 적어도 대략 그렇게. 미터법에는 많은 결함이 있지만 적어도 국제적으로는 지속적으로 결함이 있습니다.

그러나 시간이 지남에 따라 우리는 있습니다. 1 초에 1000 밀리 초, 60 초에서 1 분, 60 분에서 1 시간, 반나절마다 12 시간, 월별 약 30 일, 해당 월 및 연도에 따라 다르며, 각 국가는 다른 국가와 시간 오프셋이 있습니다. , 시간 형식은 국가마다 다릅니다.

소화해야 할 것은 많지만 길고 짧은 것은 그러한 복잡한 시나리오가 간단한 해결책을 갖는 것이 불가능합니다.

일부 모서리는 잘릴 수 있지만 더 현명한 부분이 있습니다.

여기에 대한 최고의 답변은 자정 이후에 몇 분의 정수를 저장하는 것이 완벽하게 합리적으로 보일 수 있음을 시사하지만 어려운 방법으로 그렇게하는 것을 피하는 법을 배웠습니다.

두 개의 DATETIME 값을 구현하는 이유는 정확도, 해상도 및 피드백의 증가 때문입니다.

이는 디자인이 원하지 않는 결과를 생성 할 때 매우 유용합니다.

필요한 것보다 더 많은 데이터를 저장하고 있습니까?

처음에는 내가 필요로하는 것보다 더 많은 정보가 저장되고있는 것처럼 보일 수 있지만이 히트를 가져야 할 좋은 이유가 있습니다.

이 추가 정보를 저장하면 거의 항상 장기적으로 시간과 노력을 절약 할 수 있습니다. 왜냐하면 누군가 무언가가 얼마나 오래 걸 렸는지 들었을 때 이벤트가 언제 어디서 일어 났는지 알고 싶어 할 것이기 때문입니다.

거대한 행성입니다

과거에 나는이 행성에 내 나라 외에 다른 나라가 있다는 것을 무시한 죄를지었습니다. 당시에는 좋은 생각처럼 보였지만 이로 인해 항상 문제, 두통 및 시간 낭비가 발생했습니다. 항상 모든 시간대를 고려하십시오.

씨#

DateTime은 C #의 문자열로 멋지게 렌더링됩니다. ToString (string Format) 메서드는 간결하고 읽기 쉽습니다.

예

new TimeSpan(EventStart.Ticks - EventEnd.Ticks).ToString("h'h 'm'm 's's'")

SQL 서버

또한 응용 프로그램 인터페이스와 별도로 데이터베이스를 읽는 경우 dateTimes는 한 눈에 읽을 수 있으며 계산을 수행하는 것은 간단합니다.

예

SELECT DATEDIFF(MINUTE, event_start, event_end)

ISO8601 날짜 표준

SQLite를 사용하는 경우이 기능이 없으므로 대신 텍스트 필드를 사용하고 ISO8601 형식으로 저장합니다.

'2013-01-27T12 : 30 : 00 + 0000'

노트:

• 24 시간제를 사용합니다 *

• The time offset (or +0000) part of the ISO8601 maps directly to longitude value of a GPS coordiate (not taking into account daylight saving or countrywide).

E.g.

TimeOffset=(±Longitude.24)/360 

...where ± refers to east or west direction.

It is therefore worth considering if it would be worth storing longitude, latitude and altitude along with the data. This will vary in application.

• ISO8601 is an international format.

• The wiki is very good for further details at http://en.wikipedia.org/wiki/ISO_8601.

• 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.

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.

---

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?

---

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 actually stores time as fractions of a day. For example, 1 whole day = value of 1. 12 hours is a value of 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.

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.

---

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.

---

If you are using MySQL use a field type of TIME and the associated functionality that comes with TIME.

00:00:00 is standard unix time format.

If you ever have to look back and review the tables by hand, integers can be more confusing than an actual time stamp.

---

Try smalldatetime. It may not give you what you want but it will help you in your future needs in date/time manipulations.

---

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.

---

Instead of minutes-past-midnight we store it as 24 hours clock, as an SMALLINT.

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)

Pretty goofy though ... there should have been a TIME datatype in MS SQL for many a year already IMHO ...

---

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.

A value of null means that the time hasn't been recorded yet.

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).

• 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).

• If MinutesInput = 131072..8589934591 then use Int (Note: Negate and add 2147483648 as necessary).

• If MinutesInput = 8589934592..36893488147419103231 then use BigInt (Note: Add and negate 9223372036854775808 as necessary).

• 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 stackoverflowee can finish this answer).

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).

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.

---

The saving of time in UTC format can help better as Kristen suggested.

Make sure that you are using 24 hr clock because there is no meridian AM or PM be used in UTC.

Example:

• 4:12 AM - 0412
• 10:12 AM - 1012
• 2:28 PM - 1428
• 11:56 PM - 2356

Its still preferrable to use standard four digit format.

---

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.

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.

Most languages allow the easy conversion from Ticks → TimeSpan → Ticks.

Example

In C# the code would be:

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.

Note

My personal preference however, would be to store the date and time as an ISO8601 string.

---

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

When taking this general approach people (including me!) reading the code are less confused if I use the same standard everywhere

참고URL : https://stackoverflow.com/questions/538739/best-way-to-store-time-hhmm-in-a-database