You can write Python code that works outside the context of any office suite, on all kinds of computers.
Thank you for the reference. I think the problem is resolved; The script has .loadDefaultCalendarTZ with “UTC” string in TimeZone field when if string were just “” it would return local timezone as desired. It returns UTC time with offset, not the offset itself, so I guess difference has to be calculated.
The TimeZone string is something you need to pass as an argument when using the respective methods.
Clever guess.
Function localDateTimeOffset(Optional pMode As Long)
If (pMode<0) OR (pMode>4) Then Exit Function
Const utcPrefix = " UTC"
Dim theLocale As New com.sun.star.lang.Locale
Dim theCalendar As Object
theCalendar = CreateUnoService("com.sun.star.i18n.LocaleCalendar")
With theCalendar
.loadDefaultCalendar(theLocale) REM Uses Unix NullDate!
offs = .LocalDateTime - .DateTime
End With
out = offs REM Unit is 1 d ("calendaric day")
If pMode>0 Then out = offs * 24 REM Unit is now 1 h.
If pMode>1 Then
signChar = IIf(sgn(out)<0,"-", "+")
out = signCahr & Format(Abs(offs), "H:MM") REM out now formatted to a string.
EndIf
If pMode>2 Then
If offs=0 Then out = ""
out = utcPrefix & out
EndIf
If pMode=4 Then out = utcPrefix & signChar & Format(Abs(offs * 24), "0.00""h""")
localDateTimeOffset = out
End Function
BTW: What about your profile data? We don’t play Hide’n Seek here.
The
TimeZonestring is something you need to pass as an argument when using the respective methods.
I don’t know what I said to imply that it wasn’t something that needed to be passed.
For the code:
- Why use LocaleCalendar rather than LocaleCalendar2?
- Where are LocalDateTime and DateTime documented as I can’t find them in LibreOffice: LocaleCalendar Service Reference
Search the included interfaces. Probably XCalendar … XCalendar4…
However, the API documentation won’t have a lot to say. If you inspect an instance of the localeCalendar service in the Basic IDE or with one of the famous tools, you can see these properties.
If you inspect an instance of the
localeCalendarservice in the Basic IDE
How do you do this? I can’t find the inspect feature in the documentation. What famous tools are you talking about?
@quix
There is the built in inspector → Development Tools
.
And then MRI and XRay → Apache OpenOffice Community Forum - [Solved] Object Inspection Tool: XRAY or MRI - (View topic) and need help to get MRI to work on LO 7x - #2 by Ratslinger
@Ratslinger
Thanks for info. That toggle does not appear under tools in the Basic IDE (I’ve been assuming the editor that comes up when selecting to edit macros (Tools->Macros->Edit Macros…) that has LibreOffice Basic in the window title is the Basic IDE) so I added it to the toolbar, but it’s greyed out. Is there a reason why it’s greyed out? If I open development tools in a different LibreOffice flavor it just shows objects limited to the “LibreOffice application Supported objects” table in that development tools link. I don’t know what object can be placed that would use com.sun.star.i18n.LocaleCalendar service let alone the sought after functions. I’ll try XRAY or MRI to see if it’s more useful.
@quix
Don’t know what LO version you are using. From the on-line help:
My Calc sample:
.
My personal choice of the three is MRI.
Edit:
Using MRI & @Lupp code you would inspect theCalendar
There is a tutorial link in the Apache post-> Apache OpenOffice Community Forum - [Tutorial] Introduction into object inspection with MRI - (View topic)
.
To obtain MRI use the link in my post
@Ratslinger
I had to inspect via MRI 1.3.4 library in the macro because the menu bar in its gui doesn’t seem to work. MRI shows LocalDateTime and DateTime in the properties tab as double data types, but they don’t show up in methods. So I’m still not exactly where it’s coming from because of both services being loaded (more on that later). For future people that come across this thread, formatting the output of each of the functions shows LocalDateTime gives time local to the user and DateTime gives UTC standard time.
.
I think LocalDateTime the same as getLocalDateTime (in XCalendar4 which LocaleCalendar2 inherits) and DateTime is the same as getDateTime (in XCalendar which both LocaleCalendar and LocaleCalendar inherit), but I’m not sure if LocalDateTime accounts for “The actual timezone and daylight saving time offsets effective at the given date and time are considered and added to the UTC time at the calendar.” part of documentation.
.
Question about API:
I am inspecting theCalendar with (InitializeMRI function outside scope)
Call InitializeMri
Dim theLocale As New com.sun.star.lang.Locale
Dim theCalendar As Object
theCalendar = CreateUnoService("com.sun.star.i18n.LocaleCalendar")
theCalendar.loadDefaultCalendar(theLocale)
oMRI.inspect(theCalendar)
.
Despite LocaleCalendar service being called, MRI is showing LocaleCalendar and LocaleCalendar2 as supported service names as well as getting all the methods from both services. It seems this isn’t just MRI showing it this way as functions from LocaleCalendar2 can be called. Why are both services being created/imported when only one of them is specified?
Did quick examine and used Mri to get reference for LocalDateTime and DateTime - Here are the methods:
https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1i18n_1_1XCalendar4.html#affc018d1cc480f7d136a43f691d62338
https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1i18n_1_1XCalendar.html#ac31e3b61dd515c2ebb983f55f09be2a7
You can also get each.
.
I have nothing further as I only wanted to provide some links to info I had.
@Ratslinger
When I do oMRI.inspect(theCalendar.LocalDateTime) I just get the return value of that function. How are you examining and getting the reference? Are you selecting the property/function and getting generated code snippet or double clicking the property/function? When I double click I get the return value (if theCalendar.loadDefaultCalendar(theLocale) is called; nothing seems to happen if it’s not). The generated snippet (if theCalendar.loadDefaultCalendar(theLocale) is called) just shows LocalDateTime or DateTime respectively assigned to getLocalDateTime or getDateTime and neither of the functions you linked to.
I load the Mri library then inspect after the item is declared (or elsewhere as may need):
Mri theCalendar
On property, right mouse & select IDL ref - I have my MRI set for LO.
.
Some Mri info here → Home · hanya/MRI Wiki · GitHub
@Lupp
I took what you posted and changed it for my needs so that UTC offset is standard and allows two digit hours while also ensuring the offset is properly rounded. Here it is for anyone who wants to ensure timezone offset correctness with this forsaken language. I am interested in critique, if this could be improved, if somebody gets an incorrectly calculated or rounded timezone, etc… This assumes that timezone offsets will have 15 minute offsets and more explanation in the comments.
REM ***** BASIC *****
Option Explicit
Function localDateTimeOffset(pMode As Integer)
Dim utcPrefix As String
If (pMode = 0) Then
utcPrefix = ""
ElseIf (pMode = 1) Then
utcPrefix = " UTC"
Else
MsgBox("localDateTimeOffset requires 1 for UTC prefix string or 0 for none")
Stop
EndIf
Dim aLocale As New com.sun.star.lang.Locale
Dim oLocaleCalendar As Object
Dim offset As Double
Dim fixedOffset As Double
Dim result
Dim signChar
oLocaleCalendar = CreateUnoService("com.sun.star.i18n.LocaleCalendar")
With oLocaleCalendar
.loadDefaultCalendar(aLocale) REM Uses Unix NullDate!
'.LocalDateTime is time local to user. .DateTime is UTC standard time.
'Formatting these two will see this. These are not documented functions.
'May cause rounding issues if second function lags too much
offset = .LocalDateTime - .DateTime
End With
'Some tests you can run by uncommenting and respective expected results
'offset = -0.332916666667 '7.99/24 -8:00
'offset = -0.304166666667 '7.3/24 -7:30
'offset = -0.295833333333 '7.1/24 -7:15
'offset = -0.291666666667 '7.0/24 -7:00
'offset = -0.3125 '7.5/24 -7:30
'offset = -0.520833333333 '12.5/24 -12:30
'offset = 0.691666666667 '16.6/24 +16:45:
'offset = -0.5 '12.0/24 -12:00
'offset = 0 '0.0/24 +00:00
signChar = IIf(sgn(offset)<0,"-", "+") 'Get sign indicating offset is behind or ahead of UTC to prepend to output
'Format and other functions round number so need to make sure time is properly rounded.
'Assuming timezone works in 15 minute increments (that's what the UTC offset wikipedia article says) and the datetime functions were designed with
'that assumptiom, this will make sure the offset is rounded to the intended 15 minute increment.
fixedOffset = fracCeiling(offset * 24, 15)
'HH:MM is not used because it rounds and/or doesn't round when it shouldn't
result = utcPrefix & signChar & Replace(Format(fixedOffset, "00.00"), ".", ":")
localDateTimeOffset = result
End Function
Function fracCeiling(offset As Double, increment As Double) As Double
Dim oService As Object
Dim offsetAbs As Double
Dim integerPortion As Double
Dim mantissaLowRes As Double
Dim mantissa As Double
Dim offsetRounded As Double
Dim mantissaRounded As Double
Dim result As Double
If (increment * 100 = 0) Then
MsgBox("Divide by 0 error! Time multiple can't be 0!")
Stop
EndIf
offsetAbs = Abs(offset) 'Make sure offset is positive
integerPortion = Fix(offsetAbs) 'Remove mantissa from offset
mantissaLowRes = CDbl(Format(offsetAbs, ".000")) 'Reduce precision of offset to stop unnecessary rounding
mantissa = mantissaLowRes - integerPortion 'Get the mantissa from the offset
oService = createUnoService("com.sun.star.sheet.FunctionAccess")
offsetRounded = oService.CallFunction("ISO.CEILING", Array(offsetAbs, increment / 60 )) 'Make sure the hour is rounded correctly
mantissaRounded = (oService.CallFunction("ISO.CEILING", Array(mantissa, increment / 60)) * 60) / 100 'Round the mantissa separately so it can be converted to minutes
if (mantissaRounded = .6) Then 'So that offset at ?:00 isn't ?:60
mantissaRounded = 0
EndIf
result = Fix(offsetRounded) + mantissaRounded 'Add rounded offset and mantissa back together
fracCeiling=result
End Function
Sub TestFunction
MsgBox(localDateTimeOffset(1))
End Sub
.
Remaining question about the API:
Why does creating UnoService com.sun.star.i18n.LocaleCalendar also create/import com.sun.star.i18n.LocaleCalendar2 and inherit functions from it as indicated by MRI and testing inherited functions myself?
-
Note that the two operations getting current time (
.LocalDateTimeand.DateTime) can produce slightly different results (without taking TZ offset into account; naturally, because they execute at different moments, one after the other); and so, even when your TZ co-incides with UTC (say, you are in GMT outside of DST), the difference may be non-zero (and, as far as I remember, there is no specification in which order the subexpressions are calculated; so either of them cauld be calculated first, and thus the sigh after subtraction could be either+or-). Thus, getting the sigh from unrounded offset is unreliable. -
Why ceiling? Again, due to the floating-point limited-precision operation, and unspecified order of subexpression evaluation, ceiling could happen to incorrectly round to the next multiple.
-
Converting the number to text and back to number, and calling Calc functions for rounding, is slow. Taking into account that Basic floating-point-to-integer conversions are rounding, I would recommend this function, that returns number of minutes rounded to given multiple (keeping the sign, so you may use its sign after normalization):
Function RoundToNMinutes(offset As Double, n As Integer) As Integer
RoundToNMinutes = CLng(offset * (24 * 60) / n) * n
End Function
Indeed, wall clock time doesn’t round, because 23:59:59.999 is still not midnight yet. Since offsets are durations, you need duration number format: [HH]:MM, note the square brackets. They round the times properly to the nearest, as you need, compensating the minuscule floating-point errors.
fixedOffset = RoundToNMinutes(offset, 15)
result = utcPrefix & Format(fixedOffset / 1440, "+[HH]:MM;-[HH]:MM")
Because creating any service means to LibreOffice: “create the object that registered itself as implementing this service”. And indeed, that object can (and usually does) implement many more other services. E.g., we don’t implement objects solely for legacy interfaces; since css::i18n::LocaleCalendar2 includes all interfaces included into css::i18n::LocaleCalendar, and the latter does not have own properties, any implementation of the former will naturally implement the latter.
1 Like
@mikekaganski
Thank you for the response.
- Would assigning the outputs of each to a variable fix the ordering issue (may add a delay, not sure if possible to measure or account for it)?
- I am under the impression valid timezone offsets are multiples of 15 minutes. The idea was to not only jump to the nearest multiple of 15 to satisfy the idea that a valid timezone was a multiple of 15, but if there was any imprecision from rounding or other there was a 15 multiple worth of slack. But I see that the formatting code you provided most likely covers all the cases for the rounding errors and I’m also realizing that the timezone shouldn’t be forced even if it’s supposed to be multiple of 15.
- Why do you divide and multiply by n? I get the same result multiplying offset by (24 * 60). Also for offsets like 0.691666666667 it outputs +16:36 rather than +16:45, but as said in 2 this is likely a non-issue.
- Where is that bracket functionality documented (I see the part about separating with semicolon, I’m not talking about that)? Format function documentation (Format Function) says:
- + $ ( ) space: A plus (+), minus (-), dollar ($), space, or brackets entered directly in the format code is displayed as a literal character.
Indeed, but why? A code that uses rounding-to-nearest instead of ceiling would be robust and independent of calculation order.
I divide by n and then convert to Long using CLng, which implicitly rounds; and the result (the integer!) is then multiplied by n, thus getting the correctly rounded-to-multiple value. And no, it doesn’t output “+16:36” for 0.691666666667, but +16:30 (if my code is used), which it should; no, it shouldn’t give “+16:45”, because offsets like 0.691666666667 are unexpected, and the time differences will differ by a tiny fraction of a second; so rounding-to-nearest for “16:30:00.013” or for “16:29:59.983” is the expected result; and for the former, rounding to “+16:45” would be wrong.
Exactly the Format function help has the reference to detailed Number Format Codes documentation; and the latter has a section for “Time Formats” discussing this. No, there is no discussion about rounding differences; we should add it there.
The sign is used to determine if offset is behind or ahead of UTC so order is important for this.
Appreciate the answers and clarification for the other points.
This reply has nothing to do with my question. Rounding preserves the sign.