11

XQuery offers various date/time functions like current-dateTime(), however I can't seem to find one which gives me the time in milliseconds since Epoch. Functions to extract hours, minutes and seconds seem to exist too individually.

What is the right way to get the Epoch time (i.e. unix time or similar) in XQuery?

Sofia
  • 771
  • 1
  • 8
  • 22
jbx
  • 21,365
  • 18
  • 90
  • 144

3 Answers3

27
(current-dateTime() - xs:dateTime("1970-01-01T00:00:00-00:00")) div xs:dayTimeDuration('PT0.001S')

returns the number of seconds as a duration, and then divides by 1 millisecond to get the number of milliseconds as a number.

Michael Kay
  • 156,231
  • 11
  • 92
  • 164
  • Thanks, I think there's an extra close brackets after the dateTime, and for proper execution it needs to be xs:dateTime. – jbx Sep 20 '11 at 15:56
  • Might be a good idea to consistently use either single- or double-quotation marks. If this is used as the value of the `select` attribute of an XSLT tag then it will conflict with that attribute's quotation marks. – Adam Mackler Aug 14 '14 at 12:29
  • what is this 1970-01-01? Why are you subtracting currentTime with this? – bosari Apr 25 '17 at 15:38
  • In the Unix world the beginning of the year 1970 is referred to as "the Epoch", and that's what the question asked for. – Michael Kay Apr 25 '17 at 18:50
5

thank you for the tips. I modify the code for Oracle Service Bus 11g (OSB 11g) Xpath editor in case someone else needs it

{ (fn:current-dateTime() - xs:dateTime("1970-01-01T00:00:00-00:00")) div xdt:dayTimeDuration("PT0.001S") }
Aditya
  • 757
  • 8
  • 11
  • if I use this as a unique key for a request, is there any possibility that during high load 2 requests can have same timestamp? – Pargat Jan 02 '20 at 09:22
0

Additional tricks on Aditya's answer for OSB 11g.

There has an annoying bug on XQ Editors that will change div and operator into a , (comma).

Just put a conversion function in front of that code. such as xs:long, xs:string

ex.

{ xs:long((fn:current-dateTime() - xs:dateTime("1970-01-01T00:00:00-00:00")) div xdt:dayTimeDuration("PT0.001S")) }
Wanpaya
  • 49
  • 6