Formatting timestamps

[MarkF]

Hi all,

I'm new to working with Timestamps in ODAC and I seem to be having some problems. Is there a setting for the timestamp format to use when calling ToString on a timestamp? I see from the code that there's a format property on timestamps, but I'm not sure how it gets set. Is there a Timestamp format setting on the session or datasets? It looks like if a format isn't set, then it uses the ShortDateFormat and TimeFormat globals, but it seems like they don't work with time formats like 'HH:NN:SS' (so it shouldn't show AMPM and use 24hr time.) I also get some range checking errors from TOraTimeStamp.SetFormat when I use that time format.

So I guess my questions are:

1. How do I set the Timestamp format.
2. Is there a "TimestampsAsStrings" option such that Timestamps would be bound as strings and converted to text on the server side?

Thanks!

-Mark

[MarkF]

I've managed to track things down a bit. I hadn't noticed the "TimeFormat" global variable in OraClasses. It's interesting that ShortDateFormat is being used each time, but LongTimeFormat is being buffered in a global instead of being used each time. It seems like from a thread safety issue that each session object perhaps should have a local TFormatSettings object. In any case I have figured out how to get my formats working for now it seems.

There is a small range checking problem in the TOraTimeStamp.SetFormat procedure. It indexes off the end of the string when it's checking any format that has double letters at the end (i.e. yyyy-mm-dd). It's not a big deal of course, but all those double/triple letter checks should be changed to check the index position. i.e.:

if (Str[P] = 'd') or (Str[P] = 'D') then begin

should be changed to:

if (P <= Length(Str)) and ((Str[P] = 'd') or (Str[P] = 'D')) then begin

in about 10 or so places in the procedure.

-Mark

[AlexP]

Hello,

1. You can set Timestamp format in your Query Fields as
OraQueryYOUR_TIMESTAMP_FIELD.format := 'needed format'
or in sql query as
SELECT TO_CHAR(YOUR_TIMESTAMP_FIELD,'needed format')

2. The TOraTimeStamp.AsString method converts the string on the server, but the format is set at the client side.


The Global time format variable is declared in the Delphi SysUtils unit.

if (Str[P] = 'd') or (Str[P] = 'D') then begin

The Range check error is really possible in this case, but only when the project is built with the Range checking options enabled. If these ptions are disabled, everything works correctly, because Delphi uses the ASCII 0 (NULL; #0) symbol to mark the end of string.
Thank you for discovering this bug.
We will fix it in one of the next builds/versions of ODAC.

[MarkF]

Hi Alex,

The global time format variable is in SysUtils, but ODAC doesn't use it except to initialize the global "TimeFormat" variable in the OraClasses unit as I pointed out in my first paragraph above. Is there a reason why it's buffered like that?

Thanks for fixing the range check error.

-Mark

[bork]

We stored time format to the TimeFormat global variable instead of getting it from the LongTimeFormat variable because there is a Delphi bug:

Code: Select all

  LongTimeFormat := TimePrefix + HourFormat + ':mm:ss' + TimePostfix;

Time separator is always ":" and TimeStamp has invalid display format if time format is set to "hh-mm-ss".

But the way with storing time format on application initialization is not correct as well. It can have invalid behavior if user changes Regional Settings when application is running.

I've changed the code: time format will be get from LongTimeFormat, and ":" will be replaced with correct TimeSeparator. This fix will be included in the next ODAC build.

[MarkF]

Are you sure that's a Delphi bug? The format string's separator is supposed to always be ':'. The ':' character in the ShortTimeFormat and LongTimeFormats are placeholders for the "TimeSeparator" global variable. This is also true of the '\ character in the date format strings, it is a placeholder for the DateSeparator. Both timeseparator and dateseparator are pulled from the system locale info.

From the documentation:
":" Displays the time separator character given by the TimeSeparator
global variable.

If you take a look at SysUtils.DateTimeToString you'll see that they do:

Code: Select all

          ':':
            if TimeSeparator  #0 then
              AppendChars(@TimeSeparator, 1);

In any case, it sounds like you're on the right track to improve the issue. Thanks for looking into it.

-Mark

[bork]

You can compare the LongTimeFormat variable value in Delphi with time format in Windows. Try to set the time format to "hh-mm-ss" in Windows and compare the LongTimeFormat variable value and the result of the following code execution:

Code: Select all

var
  DefaultLCID: Integer;
begin
  DefaultLCID := GetThreadLocale;
  Result := GetLocaleStr(DefaultLCID, LOCALE_STIMEFORMAT, LongTimeFormat);
end;

The LongTimeFormat variable will contain the "hh:mm:ss" value. Maybe it is not a Delphi bug, but it is a Delphi feature. But we need the Windows time format for our convertation. Now it is fixed and will work correctly.

[MarkF]

> The LongTimeFormat variable will contain the "hh:mm:ss" value.

Yes, exactly as it should. The TimeSeparator will be '-' and Delphi functions that format time will work correctly.

> Maybe it is not a Delphi bug, but it is a Delphi feature

Ok, but now you understand it I hope? It's definitely not a bug. It's fully documented and important to understand.

Thanks for fixing things.

-Mark