What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

How do I translate PRTG timestamp values format to normal time format?

Votes:

2

I need to use the timestamps that come from the sensors, and I get that information from lastdown_RAW, and lastup_RAW on the sensors content. The problem is I have no idea of how to format the timestamp that comes back. This is an example of the timestamp: 40190.5279598380. Excel seems to know how to format it, but I need to insert it straight into a database, and I need to work with the date/ time. If you could point me in the right direction, that would be great.

api format prtg raw sensor time timestamp values

Created on Feb 25, 2010 2:02:00 PM by  Patrick Hutter [Paessler Support] (7,225) 3 3

Last change on Feb 25, 2010 2:21:09 PM by  Daniel Zobel [Product Manager]



10 Replies

Accepted Answer

Votes:

1

PRTG Network Monitor uses the following definition for date and time values: The value represents the number of days that have passed since 12/30/1899 (a date defined as a standard once, which is also what triggered the panic about Y2K).

This is a notation that also exists in many currently implemented programs, e.g. Excel. If you place the value in a cell, select this cell and then change the format type for the value to a date format of your choice, it will display the date in the selected format.

Some databases, however, require the values to be computed beforehand - and not everyone uses Excel. In those cases, it makes sense to know the computational process.

Computing the value

Following are some examples of TDateTime values and their corresponding dates and times:

0      12/30/1899 12:00 midnight
2.75   01/01/1900 06:00 pm
-1.25  12/29/1899 06:00 am
35065  01/01/1996 12:00 midnight

The integral part of a value is the number of days that have passed since 12/30/1899. The fractional part of a value is the fraction of a 24 hour day that has elapsed.

To find the fractional number of days between two dates, simply subtract the two values, unless one of the TDateTime values is negative. Similarly, to increment a date and time value by a certain fractional number of days, add the fractional number to the date and time value.

Created on Feb 25, 2010 2:03:00 PM by  Patrick Hutter [Paessler Support] (7,225) 3 3

Last change on Apr 5, 2012 1:14:04 PM by  Patrick Hutter [Paessler Support] (7,225) 3 3



Votes:

2

Converting the timestamp value using vb.net

This little vb.net function converts the timestamp value to a normal date and time string:

Public Shared Function TimeRaw2String(ByVal timeRaw As String, Optional ByVal timeOnly As Boolean = False) As String Dim d As DateTime = System.TimeZone.CurrentTimeZone.ToLocalTime(DateTime.FromOADate(CDbl(timeRaw.Replace(".", System.Globalization.NumberFormatInfo.CurrentInfo.NumberDecimalSeparator)))) If timeOnly Then Return d.ToLongTimeString Return d.ToShortDateString & " " & d.ToLongTimeString End Function

Created on Mar 12, 2010 10:59:50 AM

Last change on Mar 15, 2010 10:07:25 AM by  Daniel Zobel [Product Manager]



Votes:

0

Hello, is it possible to convert this kind of timestamp with PHP or Javascript?

Created on Mar 12, 2010 1:31:31 PM

Last change on Mar 12, 2010 2:08:19 PM by  Aurelio Lombardi [Paessler Support]



Votes:

0

Converting prtg timestamp value using php

function prtg_timestamp_to_unix_timestamp ($prtg_timestamp) {
	if (!is_numeric($prtg_timestamp)) return $prtg_timestamp;
	$past = ($prtg_timestamp-25569)*86400;
	$unix_timestamp = $now-$past;
	return $unix_timestamp;
}

Created on Oct 13, 2010 1:48:44 AM

Last change on Jan 31, 2012 10:29:41 AM by  Torsten Lindner [Paessler Support]



Votes:

0

I cannot get Excel to understand the format. The way I understand the first answer it should be able to work with it nativley - but how?

I have a column with values like 414.172.868.055.556 - when I set the datatype for that column to "date" or "time" all I get is ############### ... maybe this is more of an Excel question but still.

Thanks!

Created on Jun 6, 2013 7:03:05 AM



Votes:

0

The columns that have the date and time values will have a value similar to 41430.66667 which should translate with excel into 6/5/2013. Where are you exporting this data from to get values like the one shown above?

Created on Jun 6, 2013 2:27:01 PM by  Greg Campion [Paessler Support]



Votes:

0

Since the data is the number of days since December 30, 1899, then can convert it to seconds

Examples:

  • in Javascript using this modified Gist:
// Convert Excel dates into JS date objects
//
// @param excelDate {Number}
// @return {Date}

function getJsDateFromExcel(excelDate) {

  // JavaScript dates can be constructed by passing milliseconds
  // since the Unix epoch (January 1, 1970) example: new Date(12312512312);

  // 1. Subtract number of days between Dec 30, 1899 and Jan 1, 1970            
  // 2. Convert to milliseconds.

	return new Date((excelDate - 25569) * 86400 * 1000);

> as.POSIXlt(x = 40190.5279598380 * 86400, origin = "1899-12-30")
[1] "2010-01-12 04:40:15 PST"

Created on Oct 2, 2014 10:29:44 PM

Last change on Oct 3, 2014 6:57:49 AM by  Torsten Lindner [Paessler Support]



Votes:

0

using powershell [datetime]$utc = [System.DateTime]::FromOADate(40190.5279598380)

Created on Jan 28, 2016 3:52:23 AM



Votes:

1

Or using Python:

from datetime import datetime

datetime.fromtimestamp((42743.2113998495-25569)* 86400)

Created on Jan 9, 2017 6:59:54 PM

Last change on Jan 9, 2017 7:19:52 PM by  Stephan Linke [Paessler Support]



Votes:

0

Cool, thanks for sharing! :)

Created on Jan 9, 2017 7:20:02 PM by  Stephan Linke [Paessler Support]




Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.