How to get UTC DateTime in a spreadsheet or in Basic

[Lupp]

(I'm afraid I can't clearly keep apart UTC and GMT. Anyway they shouldn't differ on the level of accuracy we can expect in a spreadsheet or in any offic application.)

Answering the question https://ask.libreoffice.org/en/question ... -utc-hour/ in a different site, a user "sokol92" posted a function returning UTC (or something nearly equivalent). Below you find what I made of it for my own use:

Code: Select all

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 For non-Calc documents the omitted pNullDate defaults to officeNulldate.
REM Otherwise the pNullDate must be passed in ISO 8601 extended.
REM Be sure to understand that a result based on a pNullDate different from
REM ThisComponent.NullDate can't reasonably be formatted as a date in Calc.
  Const unixNullDate    = "1970-01-01"
  Const officeNullDate  = "1899-12-30"
  Const starDivNullDate = "1900-01-01"
  Const  fancyNullDate  = "1904-01-01"
  Dim aLocale As New com.sun.star.lang.Locale
  Dim oLocaleCalendar As Object
  Dim nOffset As Double
  If IsMissing(pNullDate) Then pNullDate = "0" REM use ThisComponent.NullDate
  If pNullDate = "0" Then
    If NOT ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then
      pNullDate = officeNullDate
    Else
      With ThisComponent.NullDate
        pNullDate = "" & .Year & "-" & .Month & "-" & .Day
      End With
    End If
  End If
  nOffset = DateValue(pNulldate)
  oLocaleCalendar=CreateUnoService("com.sun.star.i18n.LocaleCalendar")
  With oLocaleCalendar
    .loadDefaultCalendar(aLocale) REM Uses Unix NullDate!
    nowUTC=.DateTime +  DateValue(unixNullDate) - nOffset
  End With
End Function

Interested in criticism.

The much simpler time-only function clockUTC() is contained in my next Post below.

Editing: I inserted a letter "n" which had vanished for unknown reasons since my original testing from "com.sun.star.sheet" in the code.

Edit:  dt=2022-10-31 10:40 UTC Due to questions in a different site I came back to this thread and first time checked the demo attached to the fourth post (by @Villeroy). Be careful: There only is set the Unix-Nulldate (1970-01-01), and this setting is regarded when formatting the date-part of a date-time-stamp. However (at least under Win) the NOW() function of Calc takes its result from the system's zone-time. You won't get UTC (or GMT) this way. In addition: Don't tamper with the document's NullDate if you not are absolutely sure to understand the implications concerning different functions and API means. Testing them anew the UDFs I published in this thread (nowUTC() and clockUTC()) proved correct so far.

[DiGro]

Interested in criticism.

I wouldn't dare to
Works nicely

Maybe this helps regarding the difference between the two ( GMT and UTC) https://www.timeanddate.com/time/gmt-utc-time.html:

Although GMT and UTC share the same current time in practice, there is a basic difference between the two:

GMT is a time zone officially used in some European and African countries. The time can be displayed using both the 24-hour format (0 - 24) or the 12-hour format (1 - 12 am/pm).

UTC is not a time zone, but a time standard that is the basis for civil time and time zones worldwide. This means that no country or territory officially uses UTC as a local time.

[Villeroy]

I think that in any time zone =NOW()-DATE(1970;1;1) gives the same result because the offset of both values is the same within the same time zone. Iow, the difference is the same for any time zone.

[Villeroy]

You can also set the spreadsheet's NullDate to 1970-1-1. Then NOW() represents the GMT/UTC.

[Lupp]

I have a problem with understanding.
Well, the NullDate isn't read-only. But you surely won't recommend to change the documents NullDate just to be able to omit a user function for a specific purpose.
The NullDate anyway was a secondary aspect of the goal to get GMT time without funny offsets like DST.
The means for this I found or was shown (the other thread I linked to) return the result for reasons I don't know following the Uniix standard with NullDate 1970-01-01. If I only want the time, I simply can crop the integer part. This is what the mentioned "sokol92" did based on slightly different means.
I should have posted first a time-only function, and missed that. sorry.

However, my next thought was to get a full date-time function like NOW() returning the time and the date as well regarding UTC (including the date-switch). This function is what I then named nowUTC(). Only by this intention it became necessaray to look at the NullDate settings. The UTC time I got and the current document use different NullDate. To make the result deliverable to Calc -of which I did NOT want to change the .NullDate- I needed to calculate the difference between Unix.NullDate and doc.NullDate. In a possible use for a Writer TextField the NullDate is fix (1899-12-30).

The function as it is shall return a now-result with date and time for GMT as zone disregarding the zone set for the system. This in a way that allows for formatting as usual in Calc - and Writer and I think it does. (I did not consider the pseudo TextFfileds in Draw.) If time-only is wanted, the integer part can be cut off as we do with the ordinary NOW() function in calc. (BTW: The Basic Now() is different.)

The time-only function I should have given before the nowUTC() above comes now:

Code: Select all

Function clockUTC() As Double
  Dim nullLocale As New com.sun.star.lang.Locale
  Dim oLocaleCalendar As Object
  Dim interim As Double
  oLocaleCalendar = CreateUnoService("com.sun.star.i18n.LocaleCalendar")
  With oLocaleCalendar 
    .loadDefaultCalendar(nullLocale) REM Uses Unix NullDate!
    interim  = .DateTime
    clockUTC = interim - Int(interim)
  End With
End Function

[Villeroy]

Well, the NullDate isn't read-only. But you surely won't recommend to change the documents NullDate just to be able to omit a user function for a specific purpose.

Why not? If you lots of calculations are based on a certain base date, adjusting the documents base date makes things easier.

[Lupp]

The snippet neither was about lots of calculations nor does it contain lots of calculations. (It may allow for simplifications.)

The purpose is to get a current date-time-stamp (or the time only) independent of the local or system's time-zone and of arbitrary offsets like DST. Since I regard time-only to be stunted information, and in this case the assignment to a specific "this day" would be probably unclear, I preferred the date-time-stamp which can be formatted with a suffix like UTC or GMT in any Calc sheet or Writer DateTime TextField according to the applicable NullDate.

To have such information at hand may be useful in a context where calculations and expected exchange make standard settings and formatting desirable or even essential.

Please tell me if the posted code misses its purpose under conditions, or is simply incorrect.

Somebody may want to see an example with a hypothetical use-case:

nowUTCdemo.ods

(24.66 KiB) Downloaded 1446 times

It was made with LibrOffice V7.1.1, but should also work in any version of LibO and in AOO (if macro execution is permitted).
Editing: There was a flaw in the attachment not related to the snippet. (AOO doesn't treat an expression placed in the range position of MATCH() as ForceArray.)I replaced the first attachment with a rectified one.

Edit2: The other flaw also mentioned in the starting post now was also contained in the original attachment. Sorry!
To be sure that you test one of the features relevant for Calc, you should change (and later change back!) your NullDate setting and verify that date-formatted cells containing =nowUTC() show the unchanged formatted date (at latest after a recalc).
If for special reasons an arbitrary NullDate is explicitly passed to the function, date-formatting makes no sense, of course (if not the document's NullDate is the same).

[Villeroy]

In Python that would be:

Code: Select all

import time
def gmtime():
    return time.time()/86400

wrapped into a spreadsheet-addin with localization, help strings and all the bells and wistles.

[Lupp]

It's surely quite as simple in Python, to give the corresponding date regarding the NullDate setting of (if applicable) the sheet. You remember: The date should allow to format it the ordinary way in spreadsheet cells without changing the (null) time zone or introducing an offset. In other words: I suppose, Python can as easily provide a function equivalent to the suggested nowUTC().

[Villeroy]

OK, OK

Code: Select all

import time
def gmtime():
    return time.time()/86400 + 25569

A spreadsheet add-in can determine the current NullDate, I suppose. 25569 assumes 1899-12-30

[Lupp]

... I suppose. 25569 assumes 1899-12-30

Yes. Its simply DATEVALUE("1970-01-01") with the mentioned Calc NullDate in force.
However, the correctness of the new function depends on the assumption that time.time() also contains an integer part for the date with Unix NullDate.
Is the time.time from the Python time module actually a date-time based on the unit s?

[Villeroy]

Help on built-in function time in module time:

time(...)
time() -> floating point number

Return the current time in seconds since the Epoch.
Fractions of a second may be present if the system clock provides them.

[Lupp]

"Current time" as a term for the interval from 1970-01-01 00:00:00 until now looks strange to me.
Anyway: I see resons to deprecate the day as predefined unit of time. The s is a scientific unit defined to an extreme precision. The day is volatile in more than one way. A spreadsheet second is 1/86400 disregarding this fact.
For the same reason the s is only a pseudo-unit If used to describe epoch-based calendaric time.
This isn't relevant for spreadsheet usage currently, but wait a few millennia...
Currently there were 37 leap seconds since the UTC epoch - and they are not counted by the mentioned Python time.

[karolus]

Hallo

Use the higher-level Api datetime instead time

Karolus

[Lupp]

What's the advantage?
What's a "higher-level Api datetime"?
(I used the.DateTime of the service "com.sun.star.i18n.LocaleCalendar" with empty locale.)
In what way does your suggestion solve any of the issues already mentioned as there were:

Consistency with the NullDate setting of a Calc document when formatting the result.
Unsuitability of the day as a unit of time
Unsuitability of the second as a "unit" for calendaric time (TimeOfDay)
Confusing results of shifting time forwards/backwards twice a year

Any logger (e.g.) usable to store time values based on which durations shall be reliably calculated, needs a not-shifting time (but must accept to not be able to register leap seconds).
How to register current time values the way "DST" regulations describe it (a-hour, b-hour e.g.)?
Did you look into my example .ods?

[karolus]

hello

I am compare https://docs.python.org/3/library/datetime.html to https://docs.python.org/3/library/time.html nothing else!

Compared to the naive Calc/Basic Floating-Point-number-of Days since 1890-12-30 00:00 in python exists real DateTime objects for Dates and Times versus TimeDelta-objects (durations)

Any logger (e.g.) usable to store time values based on which durations shall be reliably calculated, needs a not-shifting time (but must accept to not be able to register leap seconds).
How to register current time values the way "DST" regulations describe it (a-hour, b-hour e.g.)?

https://docs.python.org/3/library/time. ... .monotonic

[Lupp]

The only Basic function I used was DateValue(). Everything else looking a bit like Basic is just a few predefined variables and methods forming the bridge to the uno-API. Any language used to program for LibO/AOO needs such a bridge, but from the examples I saw, I would judge the bridges are less comfortable if compared with the Basic-bridge.

For the posted coce the API was what provided the relevant services, and the DateValue function of Basic with its fix NullDate could easily be replaced using the FunctionAccess service of the API (having NullDate 1899-12-30 by default, but easily set to an arbitrary NullDate).
LibO/AOO Basic itself is a poor language with many disadvantages, of course. However, no powerful programming needed here. On-board-tools indicated.

[Villeroy]

You need an add-in (extension package) in order to implement a volatile time function for a spreadsheet.

[karolus]

hello

You need an add-in (extension package) in order to implement a volatile time function for a spreadsheet.

Yes, its possible to create such AddInn-function (but we know there is roundabout factor 15 of boilerplate-code around the <10loc really need for datetime.datetime.utcnow() )

So…lets think about usecases:

• automated Data-logging: … dont reinvent wheels with corners, and use approiated tools to log into [tata] *.log files or something similar

• interactiv with User-Input:… trigger some sheetevent (eg. content_has_changed) to easypeasy python-function

[Villeroy]

There is interface XVolatileResult. You declare this as the return value of your function and UNO will call a listener whenever something changed. I think, this is easy enough.
There is still one thing, I can not get my head around:
https://www.openoffice.org/api/docs/com ... AddIn.html

Parameter types:
...
...
any
Depending on the data, a double, a string, or an any[][] will be passed. If no argument is specified in the function call, void will be passed. This allows for optional parameters.
::com::sun::table::XCellRange
for a ::com::sun::table::XCellRange interface to the source data.
::com::sun::beans::XPropertySet
for a ::com::sun::beans::XPropertySet interface to the SpreadsheetDocument making the function call. Only one parameter of this type is allowed in each function. It can be used to query document settings like SpreadsheetDocumentSettings::NullDate.

This indicates that it is possible to get the NullDate somehow without explaining how. How does this property set get into my function call?

[karolus]

Hello
( blame me … years ago I did some work on this, but actually i need to refresh my mindset…)

@volatile result: It seems Dev-Guide…Spredsheet_Add-Ins provides straight forward Example of use and Syntax in the declaring *.idl file
How to declare in the Python-AddInn-Class: actually I dont have any clue about, sorry. here is a strange looking java Example


@::com::sun::beans::XPropertySet interface to the SpreadsheetDocument:
most propably it should be the first Argument in the *.idl-file … and in case of utcnow() the only Argument , the same in the Function-signature in Python.
from the calling Calc-cell its not possible to provide such kind of Argument for natural reasons. may be ̣.idl > .urd > .rdb did pull it from some-where-out-of-the-core

sorry I have other things for today, may more at evening or later.

Edit:  ps.: the pragmatic solution for the volatile-Result-problem is, … dont implement it and invoke in spreadsheet another existing Volatile-Funktion, like @Lupp does in his Demo-sheet: Code: Select all =utcnow()+now()*0