Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the best way to store date values in string format?

I have to store date values (TDateTime) in a string format. What is the best way to do this? I considered the following approaches:

FloatToStr : looses precision, depends on locale settings

`FloatToStr' with format settings : looses precision

DateTimeToStr : depends on locale settings

DateTimeToStr with format settings : ?

Are there any other alternatives? How do they compare in terms of

  • Size in memory
  • Independence of locale settings
  • Precision
like image 802
jpfollenius Avatar asked May 19 '11 07:05

jpfollenius


People also ask

Can date be saved as string?

You can safely store dates as strings and query on them as long as they are properly formatted for date, i.e., “YYYY-MM-ddTHH:mm:ss”.

What is the best way to save date in database?

The default way to store a date in a MySQL database is by using DATE. The proper format of a DATE is: YYYY-MM-DD.


2 Answers

Use ISO-8601 format, as detailed in http://en.wikipedia.org/wiki/ISO_8601

If you need to save storage space, you can use the "compact" layout, e.g. '20090621T054523'.

You can use e.g. FormatDateTime('yyyymmddThhnnss',aDateTime) to produce it.

About time zone and localisation (from wikipedia):

There are no time zone designators in ISO 8601. Time is only represented as local time or in relation to UTC.

If no UTC relation information is given with a time representation, the time is assumed to be in local time. While it may be safe to assume local time when communicating in the same time zone, it is ambiguous when used in communicating across different time zones. It is usually preferable to indicate a time zone (zone designator) using the standard’s notation.

So you should better convert the time into UTC, then append 'Z' at the end of the timestamp. Or use +hh/-hh according to your local time zone. The following times all refer to the same moment: "18:30Z", "22:30+04", "1130-0700", and "15:00-03:30".

For a better resolution, you can add sub-second timing by adding a fraction after either a comma or a dot character: e.g. to denote "14 hours, 30 minutes, 10 seconds and 500 ms", represent it as "14:30:10,5", "143010,5", "14:30:10.5", or "143010.5". You can add several decimals to increase resolution.

If you need fast Iso8601 conversion routines (working with UTF-8 content), take a look at the corresponding part in SynCommons.pas. It's much faster than the default SysUtils functions.

PS:

If your purpose is just to store TDateTime as text in a pure Delphi application, you can use a not standard but fast:

function DateTimeToText(const aDateTime: TDateTime): string;
begin
  result := IntToStr(PInt64(@aDateTime)^);
end;

function TextToDateTime(const aText: string): TDateTime;
begin
  PInt64(@result)^ := StrToInt64Def(aText,0);
end;

Using the Int64 binary layout of TDateTime/double memory structure will be faster than any other floating-point related conversion.

like image 196
Arnaud Bouchez Avatar answered Oct 17 '22 23:10

Arnaud Bouchez


Generally I would recommend to store datetimes in ISO format as string: yyyy-mm-dd hh:nn:ss.mmmm

EDIT: if you want to minimize space, you can leave out all the separators and format it like: yyyymmddhhnnssmmmm

like image 6
Thorsten Engler Avatar answered Oct 17 '22 23:10

Thorsten Engler