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

Votes:

2

Your Vote:

Up

Down

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] (6,894) 3 3

Last change on Feb 25, 2010 2:21:09 PM by  Daniel Zobel [Paessler Support] (24,941) 3 3



6 Replies

Accepted Answer

Votes:

1

Your Vote:

Up

Down

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] (6,894) 3 3

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



Votes:

2

Your Vote:

Up

Down

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 by  PRTGToolsFamily (5,544) 3 2

Last change on Mar 15, 2010 10:07:25 AM by  Daniel Zobel [Paessler Support] (24,941) 3 3



Votes:

0

Your Vote:

Up

Down

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

Created on Mar 12, 2010 1:31:31 PM by  Erkan (0)

Last change on Mar 12, 2010 2:08:19 PM by  Aurelio Lombardi [Paessler Support] (8,369) 3 1



Votes:

0

Your Vote:

Up

Down

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 by  hang jae cho (0) 2

Last change on Jan 31, 2012 10:29:41 AM by  Torsten Lindner [Paessler Support] (19,480) 3 1



Votes:

0

Your Vote:

Up

Down

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 by  Christian Möller (0) 1



Votes:

0

Your Vote:

Up

Down

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] (2,560) 2 1



Please log in or register to enter your reply.


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.

PRTG
Network Monitor
Intuitive to Use.
Easy to manage.

150.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Visit
www.paessler.com

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

Top Tags


View all Tags