[Solved] Need Basic function to compute a Unix timestamp

Creating a macro - Writing a Script - Using the API

[Solved] Need Basic function to compute a Unix timestamp

Postby Herb40 » Thu May 18, 2017 4:18 pm

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
Herb40
 
Posts: 134
Joined: Thu May 08, 2014 3:35 am

Re: Need Basic function to compute a Unix timestamp

Postby RoryOF » Thu May 18, 2017 5:02 pm

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.3 on Xubuntu 16.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 24550
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby Zizi64 » Thu May 18, 2017 5:59 pm

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

https://forum.openoffice.org/en/forum/v ... f=13&t=606

My tip (maybe this precision is enough for you):
Code: Select all   Expand viewCollapse view
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; LibreOffice4.4.7 on Win7x64Prof.
And the portable versions: LO3.3.0-LO5.3.4 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5447
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Villeroy » Thu May 18, 2017 7:48 pm

Pass the Basic date-time in question and the time zone (-12 to 12)
Code: Select all   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 23979
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby RoryOF » Fri May 19, 2017 11:40 am

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.3 on Xubuntu 16.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 24550
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby eremmel » Fri May 19, 2017 2:29 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 903
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

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

Postby Herb40 » Fri May 19, 2017 3:50 pm

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
Herb40
 
Posts: 134
Joined: Thu May 08, 2014 3:35 am

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

Postby Zizi64 » Fri May 19, 2017 4:58 pm

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; LibreOffice4.4.7 on Win7x64Prof.
And the portable versions: LO3.3.0-LO5.3.4 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5447
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Villeroy » Fri May 19, 2017 6:34 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 23979
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Herb40 » Fri May 19, 2017 7:08 pm

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   Expand viewCollapse view
  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
Herb40
 
Posts: 134
Joined: Thu May 08, 2014 3:35 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 9 guests