[Solved] Need Basic function to compute a Unix timestamp

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

[Solved] Need Basic function to compute a Unix timestamp

Post by Herb40 »

Is there a builtin-in Basic function that can compute a Unix timestamp, given a standard Basic Date value or a DateTime struct? If not, can you provide a Basic function that computes a Unix timestamp value? All I need to include is the date; the time is not important for my application. Thanks for your help.

'Looks like I found the answer to my question. It's simply:

Dim timestamp as Long
timestamp = DateDiff("s", "01/01/1970 00:00:00", Date())

OO appears to use the same scheme as Unix, but with a different base date.
Last edited by Herb40 on Thu May 18, 2017 5:05 pm, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Need Basic function to compute a Unix timestamp

Post by RoryOF »

I cannot answer for the detail of a UNIX timestamp, but the OpenOffice BASIC time/date functions are described at
OO BASIC time and date functions
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [SOLVED] Need Basic function to compute a Unix timestamp

Post by Zizi64 »

https://en.wikipedia.org/wiki/Unix_time

viewtopic.php?f=13&t=606

My tip (maybe this precision is enough for you):

Code: Select all

REM  *****  BASIC  *****

Function UNIX_TimeStamp(MyDateTime as Double) as Long
 dim long1970 as long
 	
 	long1970 =  DateValue("1970-01-01")
 UNIX_Timestamp = DateTime2Seconds(MyDateTime - long1970)
End function


function DateTime2Seconds(MyDateTime as double) as long

 dim MyDays as long
 dim MyHours as long
 dim MyMinutes as long
 dim MySeconds as long
 
 	MyDays = int(MyDateTime)
 	MyHours = Hour(MyDateTime)
 	MyMinutes = Minute(MyDateTime)
 	MySeconds = Second(MyDateTime)
 	
 DateTime2Seconds = ((MyDays*24 + MyHours)*60 + MyMinutes) * 60 + MySeconds
end function
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Need Basic function to compute a Unix timestamp

Post by Villeroy »

Pass the Basic date-time in question and the time zone (-12 to 12)

Code: Select all

Function EpochSeconds(basT As Date, TZ As Integer) AS Long
  EpochSeconds = (basT - cDate("1970-01-01") + TZ/24) * 24 * 60 * 60
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Need Basic function to compute a Unix timestamp

Post by RoryOF »

For various medical reasons I am not thinking very straight at present: I merely query if these formulae intrinsically make the adjustment for Leap Seconds, or if some extra processing needs to be added for that adjustment?

It would be interesting to hear from Herb40 if this is the case or not.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: [Solved] Need Basic function to compute a Unix timestamp

Post by eremmel »

The OP asked for Unix timestamp, and according Unix time wiki
defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, minus the number of leap seconds that have taken place since then.
.
The Basic Date type states:
OpenOffice.org Basic uses an internal format
without any details. So it is unknown if this internal format is UTC, Unix-timestamp based or based on a time-zone. Based on Villeroy's function it looks like a Date matches with a date as used in Calc and therefor it is equivalent with UTC.

Leap seconds are introduced to keep our time representation in sink with our solar time. This is artificial because an constant moving object will not go faster over a time interval that includes a leap second (the difference of official time representation between start and end date/time is one second shorter) . Thinking in this way leap seconds are only of interest to represent time stamps an not of representing 'physical' time intervals. So having a internal format like Unix timestamp or Calc is rather save to do time delta calculations.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

Re: [Solved] Need Basic function to compute a Unix timestamp

Post by Herb40 »

RoryOF wrote:For various medical reasons I am not thinking very straight at present: I merely query if these formulae intrinsically make the adjustment for Leap Seconds, or if some extra processing needs to be added for that adjustment?

It would be interesting to hear from Herb40 if this is the case or not.
I don't know whether Leap Seconds are accounted for in either the UNIX timestamp computations or in the various functions presented here. For my application, I am interested only in the number of days and not the time of day. If I were using my timestamps only within my applications and not in relation to the "outside world," any difference wouldn't matter as long as I could convert a Date to a timestamp and back again correctly. But I need a UNIX timestamp in order to specify dates to an external web site, so I am interested in whether my approach using DateDiff() gives the same result as theirs. I haven't been able to verify that yet. However, I have tried the various websites that provide conversions from dates to timestamps and have found that our results agree, so I am pretty sure that my approach is sufficient.

For those interested in UNIX timestamps involving minutes and seconds, I suggest that they do some experimenting to verify that the approach they are using gives the same result as other applications they are using.
OpenOffice 4.1.3 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Need Basic function to compute a Unix timestamp

Post by Zizi64 »

Here is some clarification (I hope):


- The Date, the Time and the datetime values are numbers in the AOO/LO, in the Windows and in the Linux, and in the Unix operating system.

- The date type strings are formatted numbers (for Windows type datetime) :
Formatted string "2017 may 19. 16:43:17" = unformatted number 42874,69672
The date part equals
2017 may 19 = 42874
and the time part
16:43:17 = 0,69672

- All of the "pure" time values contain a date part with value 0.

- The 0 value represents the base Date/Time value in all of the system.

- The windows dates are on day basis (the elapsed days since the base date are the integer part of a DateTime value, and the fraction represents the time value)

- There are three different base dates in the Windows computer world: it is a heritage from the older systems, and softwares:
1899 dec 30.
1900 jan 01.
1904 jan 01.

- The default base date in the AOO/LO (on Windows) is the first in the list above

- The Unix dates are on second basis: it is a long type integer number, represents the elapsed time (in second unit) since the Unix base date

- The base date on Unix is 1970 jan 01.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Need Basic function to compute a Unix timestamp

Post by Villeroy »

Subtract two Basic dates from each other and you get the number of days.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

Re: [Solved] Need Basic function to compute a Unix timestamp

Post by Herb40 »

Villeroy wrote:Subtract two Basic dates from each other and you get the number of days.
Yes, that's what DateDiff does, and also give you a UNIX timestamp if the starting date is Jan. 1, 1970 and the first argument is "s".

A more efficient approach if the time is not of importance is to use:

Code: Select all

  Dim myDate as Date  ' my date to be converted to a Unix timestamp
  Dim baseDate as Date  ' Unix timestamp base date (1/1/1970)
  Dim UnixDate as Double  ' the equivalent Unix timestamp of myDate

  baseDate = DateSerial(1970, 1, 1)
  UnixDate = (myDate - baseDate) * 86400
Of course, when sending these timestamps to another site, they typically must be converted to their string representations.
OpenOffice 4.1.3 on Windows 10
Post Reply