• Skip to Search
  • Skip to Content
  • Skip to Side Navigation
Getting StartedSDK ReferenceGlossary
  • Home
  • Getting Started
  • SDK Reference
  • Portal
  • How-To
  • Troubleshooting
  • FAQs
  • Reference
  • Glossary
How-To Guides
  • API Usage
  • Artificially lower bandwidth or bitrate
  • Set up publishing or subscribing for only audio or only video
  • Set up High Availability
  • Optimal RTMP encoder settings
  • How do I find my stream key?
  • Preventing stream sharing
  • Debug tokens in the Portal
  • Display multiple copies of the same stream
  • Find the video dimension of the stream
  • Measure timing
  • Create test input
  • Verify encoder ability to contribute via UDP
  • Manage issues with installing Phenix SDKs
  • RTSP ingest from IP cameras
  • Work with timestamps in reports
  • How do I deal with corporate firewalls?
  • How do I record streams for VOD playback?
  • How do I set up ad insertion?
  • How do closed captions work in the Phenix system?
  • How do I set up and use SRT Ingest?
  • How do I set up and use RTMP Ingest?
  • How do I set up and use replay?

Working with timestamps in reports

The dates in our reports (e.g., publishing and subscribing) use standard ISO time format:

YYYY-MM-DD:hh:mm:ss.SSSZ

where Z indicates the UTC or "Zulu" time zone.

UNIX Epoch

This will give you the time in seconds since January 1, 1970 (UNIX Epoch), which can either be used for your calculations or converted to a different time and date format.

For example, 2021-01-16T00:21:24.355Z results in 1610756484.

The formulas given below assume a base date of 1900-01-01, which is always the case for Excel on Windows and sometimes the case for Excel on a Mac.

If you are using a version of Excel that uses a base date of 1904-01-01, either change 25569 to 24107, or change the date base used by Excel on your Mac to 1900 by unchecking "Use the 1904 date system" in the Preferences > Calculation menu.

For example, in the Publishing report, the StartTimestamp (currently column Q) formula would be:

=(DATE(LEFT(Q2,4),MID(Q2,6,2),MID(Q2,9,2))+TIME(MID(Q2,12,2),MID(Q2,15,2),MID(Q2,18,2))-25569)*86400

and the EndTimestamp (column R) formula would be:

=(DATE(LEFT(R2,4),MID(R2,6,2),MID(R2,9,2))+TIME(MID(R2,12,2),MID(R2,15,2),MID(R2,18,2))-25569)*86400

You can double-check the calculation by entering the value into an online epoch validator and verifying that it matches the value provided in the report.

Make sure the validator you choose is set to use UTC as the basis for the human-readable output.

Excel Date or Time

This is similar to the above, but the resulting value will be in Excel date format (i.e., the number of days since the base date). The output shown in the cell will depend on the number format of the cell, and can be manipulated in Excel as you would any other date.

To convert the timestamp to an Excel date, you can use an Excel formula such as:

=(DATE(LEFT(Q2,4),MID(Q2,6,2),MID(Q2,9,2))+TIME(MID(Q2,12,2),MID(Q2,15,2),MID(Q2,18,2)))

This output can be validated by comparing it to the input timestamp, as it is more human-readable than the UNIX epoch. For example, 2021-01-13T18:34:47.223Z results in a value of 44209.77416, and changing the cell's number format to Date Time results in 1/13/21 6:34 PM.

Instructions

  1. Copy the formulas into the last columns of the downloaded report.

  2. Copy the formulas down the columns and make sure that the cell references increment correctly (e.g., cell AK34 references cell Q34).

  3. Use the values to find durations, convert to other time formats, and so on.

Page Content
    Copyright 2023 © Phenix RTS
    Privacy Policy | Terms of Service
    v2023-01-31T21:25:10