What DateTime format does hangfire use to store dates in data base? Ticks? Milliseconds?

question
1

I need to query database directly but can’t guess Hangfire datetime format:

“EnqueuedAt”:“1588651278053” is this number Ticks? or milliseconds? other?

“StartedAt”:“1588737600064” other example

Thanks

2

Hi Anybody!
Could you respond to this important question?

3

I am not associated with Hangfire, but from looking through the source code, I believe this number (such as “1588651278053”) is the number of milliSeconds since the base (or “epoch”) date of 1-Jan-1970.

In Microsoft SQL Server, in theory, you could use the DATE_ADD function to add this number of milliSeconds to the “1-Jan-1970” base date to find the corresponding date/time; however, the DATE_ADD function only accepts “int” values, and the number above will cause an overflow error.

Alternatively, you could split the number into a “seconds” value and “milliSeconds” value (“1588651278” and “053” for the number shown on the first line above), and then derive the corresponding date as follows using the DATE_ADD function twice to sequentially add the “Seconds” and “milliSeconds” values:

SELECT DATEADD(ms,53,DATEADD(s,1588651278,‘1-Jan-1970’));

This produces the corresponding date/time of “2020-05-05 04:01:18.053” for this numeric value.

4

Adding to my previous reply, here is some sample code for a Microsoft SQL Server function that should convert a Hangfire timestamp (as listed for “EnqueuedAt” etc.) to a date/time value.

From looking at the source code, I believe the timestamp value is the number of milliSeconds since 1-Jan-1970. Since this value for recent dates will overflow the DATEADD function when used as a milliSeconds value, we can divide it by 1000 to obtain the number of seconds and add that number of seconds to the base date, then find the “remainder” (or modulo) base 1000 for the remaining number of milliSeconds and add that value as well.

CREATE FUNCTION DateTimeFromHangfireTimestamp (@HFEpochTime as bigint)
RETURNS DATETIME2(7)
AS
BEGIN
RETURN DATEADD(ms, @HFEpochTime % 1000, DATEADD(s, @HFEpochTime / 1000, ‘1-Jan-1970’));
END;

In SQL Server, DATEADD will only use the integer portion of the number being added inside the function. Also note that this code will only work for dates before early 2038, after which the number of seconds will cause an integer overflow in the DATEADD function.

If you want to handle dates later than the year 2038, you can use the following code - which first adds the (integer) number of hours from the timestamp, then the remainder (integer) number of seconds, then the remainder number of milliSeconds:

CREATE FUNCTION DateTimeFromHangfireTimestamp (@HFEpochTime as bigint)
RETURNS DATETIME2(7)
AS
BEGIN
RETURN DATEADD(ms, @HFEpochTime % 1000, DATEADD(s, @HFEpochTime % (3600 * 1000) / 1000, DATEADD(hh, @HFEpochTime / (3600 * 1000), ‘1-Jan-1970’)));
END;

I am not associated with the Hangfire project, except as a user, so please review this code before using it.