Show a TimeStamp as Date Time

Time properties in Journey Analytics are collected under the event_time_ts field, to the millisecond level. When queried , it presents the time in YYYY-MM-DD format. You can format it differently using format_utc_usec, as shown in the sample syntax below.

Querying different timeframes can allow you to identify certain trends, such as daily trends or hourly trends, or to get more detailed results (for example, by the minute or second).

Syntax:

left(format_utc_usec (event_time_ts),19) as DateTime

screen 1

Tips/Best Practices:

  • Entering only (event_time_ts) in the query above will show only the date (in this example, 2014-05-14). To see the complete string including the time, enter the entire code string as shown above.

  • The 19 in the example code above indicates 19 characters for the USER_TIME string, and returns the USER_TIME in seconds, as shown in the results in the screen shot above. Use 16 characters for the minute level (i.e., 2014-05-14 18:24), and 13 characters for the hour level (i.e., 2014-05-14 18).