You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@jmeter.apache.org by Steve Kapinos <St...@tandberg.com> on 2008/11/17 15:33:42 UTC
timestamp field in xml result files
I'm trying to post process some result files which were saved to xml and
having some trouble with the ts field. I assume this to be a unix
timestamp, but can't convert it successfully to a format excel likes and
can't find any additional details in jmeter.properties as per the
documentation.
What format is this ts field stored in?
Thx
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: timestamp field in xml result files
Posted by sebb <se...@gmail.com>.
On 17/11/2008, Steve Kapinos <St...@tandberg.com> wrote:
> I'm trying to post process some result files which were saved to xml and
> having some trouble with the ts field. I assume this to be a unix
> timestamp, but can't convert it successfully to a format excel likes and
> can't find any additional details in jmeter.properties as per the
> documentation.
>
> What format is this ts field stored in?
http://jakarta.apache.org/jmeter/usermanual/listeners.html#attributes
> Thx
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: timestamp field in xml result files
Posted by sebb <se...@gmail.com>.
The formula:
=(((X)/1000+((365*70+17)*86400))/86400)+2
is the same as
=(((X)/1000+((365*70+19)*86400))/86400)
which is the same as
=X/(1000*86400) + 25569
The number 25569 agrees with the Excel formula =DATE(1970,1,1)
expressed as a number.
What the above is doing is:
* Convert X from milliseconds to days (86400 = number of seconds in a day)
* Add the Excel offset for Jan 1, 1970.
Since JavaTime 0 (and Unix time 0) are both Jan 1, 1970, but Excel
uses an earlier date, you need to add whatever value Excel uses for
Jan 1, 1970, i.e. 25569.
I can confirm that the formula also works in OOo Scalc.
For Unix => Excel, the formula is
=X/86400 + 25569
because Unix times are in seconds.
I don't know why the original poster apparently gets better results
with adding 25567, rather than 25569. Does not seem right to me now.
On 18/11/2008, Mohamed Niyas <ni...@gmail.com> wrote:
> Hi Steve,
>
> Sometimes back i also came across to findout the timestamps conversion in
> XL...
> You can use the below in Excel, which it will get the right datetime
> conversions...
> But i dont know about the Unix timestamp.
>
> *Convert Timestamps to Excel Format* :
>
> Once the data is in Excel, I convert the timestamp column from Jmeter's Unix
> timestamp
> format (base year 1970) to the Excel format (base year 1900) using this
> following formula.
> This formula is applied to the entire timestamp column.
>
> For GMT time
>
> =(((X)/1000+((365*70+17)*86400))/86400)+2
>
> For local time (replace t with your current offset from GMT)
>
> =(((X)/1000+(t * 3600)+((365*70+17)*86400))/86400)+2
>
> I have cross checked this validations... You can try out...
>
> Regards
> Mohamed Niyas M
> niyas.mohd@gmail.com
> On Tue, Nov 18, 2008 at 5:24 AM, Steve Kapinos
>
> <St...@tandberg.com>wrote:
>
>
> > According to
> > http://office.microsoft.com/en-us/excel/HP052006741033.aspx?pid=CH010004931033Excel uses a base of Jan 1, 1900 and counts DAYS not seconds using a real
>
> > number.
> >
> > >> I'm not quite sure why this vs just =UnixTime / 86400 + 25569
> >
> > >Where does 25569 come from?
> >
> > Saw it here - and on many other pages
> > http://excel.tips.net/Pages/T002051_Converting_UNIX_DateTime_Stamps.html
> >
> > But that didn't work.. the more complex one did..
> >
> > -Steve
> >
> > > -----Original Message-----
> > > From: Steve Kapinos [mailto:Steve.Kapinos@tandberg.com]
> > > Sent: Monday, November 17, 2008 9:34 AM
> > > To: JMeter Users List
> > > Subject: timestamp field in xml result files
> > >
> > > I'm trying to post process some result files which were saved to xml and
> > > having some trouble with the ts field. I assume this to be a unix
> > > timestamp, but can't convert it successfully to a format excel likes and
> > > can't find any additional details in jmeter.properties as per the
> > > documentation.
> > >
> > > What format is this ts field stored in?
> > >
> > > Thx
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> > >
> > >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> >
> >
>
>
>
> --
> Regards,
> Mohamed Niyas M
> Mobile: 98804 59080 niyas.mohd@gmail.com
> to_niyas@yahoo.co.in
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: timestamp field in xml result files
Posted by Mohamed Niyas <ni...@gmail.com>.
Hi Steve,
Sometimes back i also came across to findout the timestamps conversion in
XL...
You can use the below in Excel, which it will get the right datetime
conversions...
But i dont know about the Unix timestamp.
*Convert Timestamps to Excel Format* :
Once the data is in Excel, I convert the timestamp column from Jmeter's Unix
timestamp
format (base year 1970) to the Excel format (base year 1900) using this
following formula.
This formula is applied to the entire timestamp column.
For GMT time
=(((X)/1000+((365*70+17)*86400))/86400)+2
For local time (replace t with your current offset from GMT)
=(((X)/1000+(t * 3600)+((365*70+17)*86400))/86400)+2
I have cross checked this validations... You can try out...
Regards
Mohamed Niyas M
niyas.mohd@gmail.com
On Tue, Nov 18, 2008 at 5:24 AM, Steve Kapinos
<St...@tandberg.com>wrote:
> According to
> http://office.microsoft.com/en-us/excel/HP052006741033.aspx?pid=CH010004931033Excel uses a base of Jan 1, 1900 and counts DAYS not seconds using a real
> number.
>
> >> I'm not quite sure why this vs just =UnixTime / 86400 + 25569
>
> >Where does 25569 come from?
>
> Saw it here - and on many other pages
> http://excel.tips.net/Pages/T002051_Converting_UNIX_DateTime_Stamps.html
>
> But that didn't work.. the more complex one did..
>
> -Steve
>
> > -----Original Message-----
> > From: Steve Kapinos [mailto:Steve.Kapinos@tandberg.com]
> > Sent: Monday, November 17, 2008 9:34 AM
> > To: JMeter Users List
> > Subject: timestamp field in xml result files
> >
> > I'm trying to post process some result files which were saved to xml and
> > having some trouble with the ts field. I assume this to be a unix
> > timestamp, but can't convert it successfully to a format excel likes and
> > can't find any additional details in jmeter.properties as per the
> > documentation.
> >
> > What format is this ts field stored in?
> >
> > Thx
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
--
Regards,
Mohamed Niyas M
Mobile: 98804 59080 niyas.mohd@gmail.com
to_niyas@yahoo.co.in
RE: timestamp field in xml result files
Posted by Steve Kapinos <St...@tandberg.com>.
According to http://office.microsoft.com/en-us/excel/HP052006741033.aspx?pid=CH010004931033 Excel uses a base of Jan 1, 1900 and counts DAYS not seconds using a real number.
>> I'm not quite sure why this vs just =UnixTime / 86400 + 25569
>Where does 25569 come from?
Saw it here - and on many other pages http://excel.tips.net/Pages/T002051_Converting_UNIX_DateTime_Stamps.html
But that didn't work.. the more complex one did..
-Steve
> -----Original Message-----
> From: Steve Kapinos [mailto:Steve.Kapinos@tandberg.com]
> Sent: Monday, November 17, 2008 9:34 AM
> To: JMeter Users List
> Subject: timestamp field in xml result files
>
> I'm trying to post process some result files which were saved to xml and
> having some trouble with the ts field. I assume this to be a unix
> timestamp, but can't convert it successfully to a format excel likes and
> can't find any additional details in jmeter.properties as per the
> documentation.
>
> What format is this ts field stored in?
>
> Thx
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: timestamp field in xml result files
Posted by sebb <se...@gmail.com>.
On 17/11/2008, Steve Kapinos <St...@tandberg.com> wrote:
> Found my answer in a google search
>
> =(x/1000+((365*70+17)*86400))/86400
>
= x/86400000 + 25567
> I'm not quite sure why this vs just =UnixTime / 86400 + 25569
Where does 25569 come from?
> But the top one seems to work
>
What is the Excel base?
The JMeter base is the Java base which is milliseconds since midnight
Jan 1, 1970 UTC.
I think Unix is the same, except it uses seconds?
>
> -Steve
>
>
> -----Original Message-----
> From: Steve Kapinos [mailto:Steve.Kapinos@tandberg.com]
> Sent: Monday, November 17, 2008 9:34 AM
> To: JMeter Users List
> Subject: timestamp field in xml result files
>
> I'm trying to post process some result files which were saved to xml and
> having some trouble with the ts field. I assume this to be a unix
> timestamp, but can't convert it successfully to a format excel likes and
> can't find any additional details in jmeter.properties as per the
> documentation.
>
> What format is this ts field stored in?
>
> Thx
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
RE: timestamp field in xml result files
Posted by Steve Kapinos <St...@tandberg.com>.
Found my answer in a google search
=(x/1000+((365*70+17)*86400))/86400
I'm not quite sure why this vs just =UnixTime / 86400 + 25569
But the top one seems to work
-Steve
-----Original Message-----
From: Steve Kapinos [mailto:Steve.Kapinos@tandberg.com]
Sent: Monday, November 17, 2008 9:34 AM
To: JMeter Users List
Subject: timestamp field in xml result files
I'm trying to post process some result files which were saved to xml and
having some trouble with the ts field. I assume this to be a unix
timestamp, but can't convert it successfully to a format excel likes and
can't find any additional details in jmeter.properties as per the
documentation.
What format is this ts field stored in?
Thx
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org