LibreOffice Calc Macro, how do I get UTC hour?

[tonyburnaby]

I was hoping there is a function like: hour(utcNow())

This document mentioned “uno” dates, and a structure member “IsUTC”

But I am not able to find anything else or related sample codes

Thanks

[sokol92]

Since LO 6.3

Function getUTCTime() As Double
  Dim aLocale As New com.sun.star.lang.Locale
  Dim oLocaleCalendar2 As Object
  oLocaleCalendar2=CreateUnoService("com.sun.star.i18n.LocaleCalendar2")
  With oLocaleCalendar2
    .loadDefaultCalendarTZ(aLocale, "GMT") 
     getUTCTime=.getLocalDateTime-Int(.getLocalDateTime)
  End With  
End Function

Sub TestgetUTCTime
  Msgbox Format(getUTCTime, "hh:mm:ss")
End Sub

[mikekaganski]

Nice!

This:

getUTCTime=.getLocalDateTime-Int(.getLocalDateTime)

has a potential of returning wrong result, when the two calls happen across midnight (one before, the other after). It would show OK as string, but will introduce an error of 24 hours when used for calculations.

It’s better to save the result of a single call to a variable, or use

getUTCTime=.getLocalDateTime Mod 1

And why use “GMT”, when you may use getDateTime to get UTC, as the question stands

[Lupp]

Thanks a lot! Didn’t know. The following code based on your insight also works in LibreOffice V3.3 and in AOO.

Function nowUTC(Optional pNullDate As String) As Double
REM The function shall regard the document's NullDate if it is called from Calc.
REM without passing a pNullDate (factually "0"). 
REM Otherwise the pNullDate must be passed in ISO 8601 extended.
  Dim aLocale As New com.sun.star.lang.Locale
  Dim oLocaleCalendar As Object
  Dim sOffset As String, nOffset As Double
  If IsMissing(pNullDate) Then pNullDate = "0"
  If pNullDate = "0" Then
    With ThisComponent.NullDate
      sOffset = "" & .Year & "-" & .Month & "-" & .Day
    End With
  End If
   nOffset = DateValue(sOffset)
  oLocaleCalendar=CreateUnoService("com.sun.star.i18n.LocaleCalendar")
  With oLocaleCalendar
    .loadDefaultCalendar(aLocale)
     nowUTC=.DateTime +  DateValue("1970-01-01") - nOffset
  End With
End Function

[sokol92]

Thank you colleagues for your valuable comments!

[Lupp]

Thanks again to @sokol92.
Sorry. The code posted in my previous comment had some flaws (sOffset !)
A version reworked with that respect and slightly enhanced you find here: How to get UTC DateTime in a spreadsheet or in Basic (View topic) • Apache OpenOffice Community Forum .
There may also be a discussion at some time.

[sokol92]

Another variant of the function.

Function getUTCDateTime() As Date
  Dim aLocale As New com.sun.star.lang.Locale
  Dim oLocaleCalendar2 As Object
  oLocaleCalendar2=CreateUnoService("com.sun.star.i18n.LocaleCalendar2")
  With oLocaleCalendar2
    .loadDefaultCalendarTZ(aLocale, "UTC") 
     getUTCDateTime=DateSerial(1970,1,1) + .getLocalDateTime
  End With  
End Function

Sub TestgetUTCDateTime
  Msgbox getUTCDateTime
End Sub

[CaveMan]

Neat. Solution didnt work but the variant above this comment worked for me on LO v7 in Writer not Calc for both Windows and Linux.

[sokol92]

Curious! Let’s check together. I have the expected result.
TestUTC.ods (9.5 KB)

Version: 7.3.4.2 (x64) / LibreOffice Community
Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5
CPU threads: 6; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded

[CaveMan]

Sorry it was the variant that worked not the original solution. updated previous comment to explain.

[erAck]

No such thing in BASIC.

[Ratslinger]

Hello,

Just tried this in Win 10 and seems to work:

https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=98562#p473399

[tonyburnaby]

yeah, I am using Linux, tried that code, didn’t work. Thanks all the same.

[Wanderer]

But the python-script at the same link should be portable.

J.

[Ratslinger]

@tonyburnaby,

Just tested the python script using Ubuntu 20.04 with LO v7.1.1.2 and had no issues.

In future please include basic information with questions such as OS & LO version.

[tonyburnaby]

J. yeah, I saw that too, unfortunately that is beyond my coding skills thanks for pointing that out though.

Ratslinger, Thanks for the effort, yeah, will remember to include OS and LO versions for future questions