jMeter timestamp to Date time

By default, jMeter uses Java’s System.currentTimeMillis() to time stamp each row in its result files. There are various options in the jMeter properties file to change this default behavior and output a formatted date. However if you have result files already with the millisecond time stamp format you might need to convert it to a date time format in code or Excel.

System.currentTimeMillis() returns the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC which is known as Unix time.

Example timestamp: 1594108721012

UTC Date and time:            Tue Jul 07 2020 07:58:41
Local Date and time (UTC+1h): Tue Jul 07 2020 08:58:41

A really useful site when working with these values is https://currentmillis.com. There are lots of examples and I found it useful to check my working out.

Java

Java’s Date class has a constructor that takes the timestamp as a long, so it’s easy to create a Date object and then format the output as you like:

long timestamp = 1594108721012l;

var dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
var dateTimeString = dateFormat.format(new Date(timestamp));

System.out.println(dateTimeString); // 2020-07-07 07:58:41.012

Go

Go has a function time.Unix, but you only have the option of supplying a value in seconds or nanoseconds. For milliseconds, you have to convert it first by multiplying the timestamp value by 1000,000 which is the number of nanoseconds in a millisecond:

package main

import (
    "fmt"
    "time"
)

func main() {
    var timestamp int64
    timestamp = 1594108721012

    datetime := time.Unix(0, timestamp*int64(1000_000))

    fmt.Println(datetime.String()) // 2020-07-07 08:58:41.012 +0100 BST
}

The go-timemilli module will do this for you with a slight performance improvement.

Excel

This is the fun one…

By default, Excel uses the 1900 date system. In the 1900 date system, dates are calculated by using January 1, 1900, as a starting point. This means we need to add the value of epoch to our time stamp value.

Milliseconds in a day       = 86400000 milliseconds
Unix epoch = DATE(1970,1,1) = 25569    days

So to convert the timestamp we need to get the nuber of days in the timestamp and then add the number of days between 01/01/1900 and 01/01/1970:

=(timestamp / 86400000) + 25569 = 07/07/2020 07:58:41.012 

To display the date time correctly you need to set the format of the calculation field to be a custom format of: dd/mm/yyyy hh:mm:ss.000

This though does not take local time into account. To include local time we must add the number of hours (in milliseconds) to the time stamp:

Milliseconds in an hour = 3600000

=((timestamp + (TimezoneOffset * 3600000)) / 86400000) + 25569

eg. British Summer time (UTC+1h)

=((timestamp + (1 * 3600000)) / 86400000) + 25569 = 07/07/2020 08:58:41.012