Date Time Stamp Formatting for import

Date Time Stamp Formatting

Date time stamps are at the heart of any investigation. They are critical component to knowing when something occurred, and no investigation is complete without them!

Having the proper time stamp formats in your load file is critical to ensure successful ingestion of your metadata. 

ESI Analyst can recognize almost any Date Time format string value ranging from a full date string such as "Saturday, May 22nd, 2021 12:22:22 PM" to "2021-05-22 12:22:22 PM".

The 2 most common formats for supplying a date time are often the following:
  1. yyyy-mm-dd hh:mm:ss
  2. mm/dd/yyyy hh:mm:ss
If the date separator is a forward slash ("/"), then the first digits of the date stamp will be assumed to be the month (e.g. mm/dd/yyyy). If the date separator is a dash ("-"), then the first digits will be interpreted as the year (e.g. yyyy-mm-dd). This is important when working with date stamps that provide a 2-digit value for the year.

For reference to the standard we comply with of ISO 8601, please see article here.

Time stamps are best supplied in a hh:mm:ss format. ESI Analyst does not work with milliseconds. 

Time Stamp Exceptions

For Transactions, there may not be a time stamp associated with certain types of banking records, for example when a check was deposited. Although most banking institutions now have mechanisms for capturing a timestamp, manual transactions may just record the date. To accommodate this anomaly, Transaction metadata imports have a field entitled “Has_Timestamp”. By default this is set to a “True” value (it can be supplied as 1/0, Yes/No or True/False). If this flag is set to "False", the timestamp on the metadata item will be ignored and a value of 12:00:00 PM will be used. This allows for proper adjustment for end-user time zones, keeping the Transaction relative to the end-user.

If you are using Microsoft's Excel to format your load file beware that Excel likes to drop the seconds off of the timestamp! Unless you modify your date time format directly in Excel this can be problematic. To prevent Excel from munging dates, you can use a "T" value between the date and time values (e.g. "yyyy-mm-ddThh:mm:ss" - in Excel you must select "custom" format an input this as the pattern)​

    • Related Articles

    • Searching Including the Date Filter

      In various screens throughout ESI Analyst, there will be a date filter option to limit the view to a specific date range.  As an example, when in Review Project Metadata, you can filter by date by clicking Date Time Stamp and you will see the a ...
    • How To Set Your Timezone

      Many of the displays in ESI Analyst provide an adjusted time stamp so that a user can see time stamps relative to their own timezone. This is useful in investigations to see realtime activities as they correlate to a specific timezone and useful ...
    • Setting the Timezone of Metadata During Import

      Whether you are adding the time zone in ESI Analyst or is in your load file, the proper timezone is very important. ESI Analyst standardizes all timestamps for searching to UTC ("Coordinated Universal Time" - ...
    • Identifying Common Communications Among Actors in a Date Range

      The Common Communication Analysis Tool allows you to visualize communication patterns for a set of Actors to identify where overlap may occur. The data visualization report rapidly demonstrates where common spikes in communications occurred between a ...
    • Logging into ESI Desktop for the First Time

      Launching ESI Desktop After you have downloaded and installed ESI Desktop for the first time, you can launch the program from your desktop shortcut or "Run as administrator" from the applications folder if needed.  When you first launch ESI Desktop ...