---

Introduction

When processing large amounts of log data it is often the case that time stamps are represented in a Unix Time format, which is essentially a large integer representing the number of seconds since January 1st, 1970.

Whilst sort-able, this method of representing time is not very human friendly and requires further processing to, for example, group into years and months.

Recently a customer asked how to convert a Unix Time format into a Date and Time column in Rons Data Edit, so here we are.

For an overview of Rons Data Stream concepts see the Quick Start Quide.

Context

Convert:

TimeStamp,Value 1,Value 2
1728991800,0.0067535,0.0067545
1728993600,0.00675,0.0067565
1728995400,0.0067535,0.0067665

Into:

Date,Time,Value 1, Value 2
2024.10.15,11:30,0.0067535,0.0067545
2024.10.15,12:00,0.00675,0.0067565
2024.10.15,13:00,0.0067625,0.0067625

Using a date format of 'YYYY.MM.DD' and a time format of 'HH:MM'

Steps

Open Rons Data Stream

Of course, having downloaded and installed Data Stream from Rons Place Software, open the application by double clicking the desktop icon, or opening it from the Window start menu.

Create a Job

Create a new job

From the 'File' menu, the 'Home/Data Job' menu or the home page click 'New Job...' will will open an empty Job.

Note: Saving the job immediately, relative to your source data files will mean that all subsequent paths used in the job can be set relative to it, making the job and data portable.

Add a Source Container

From the 'Job Palette', under 'Source Containers', click 'Directory'. This will allow us to set the location of our CSV files.

Note: The '.' is used to show the source files are in the same folder as the Job file.

Add a source profile

From the 'Job Palette', under 'Source Profiles', click 'CSV'. This will allow us to establish which files are processed from the source and how they are processed.

Add an Output container

From the 'Job Palette', under 'Output Containers', click 'Directory'. This will allow us to set the location of our CSV files.

Note: The '.' is used to show the target files are in the same folder as the Job file. This might not always be ideal, but for the scope of this article we will keep it simple.

Add a Target Format

From the 'Job Palette', under 'Output Formatters', click 'CSV'. This will allow us to establish in what format the processed files are written.

Create a Cleaner

From the 'Job Palette', under 'Cleaners', click 'Embedded Cleaner'. The Cleaner is the set of change rules that will be applied to each file that is processed by the job.

This is most complex part of the process and we need to work within the rules so its worth planning out what needs to be done first. Our plan of action will be as follows:

1. Create a 'Column Selector' to select the 'TimeStamp' column.
2. Using the 'Copy Column' rule copy the 'TimeStamp' column to a 'Date' column.
3. Using the 'Copy Column' rule copy the 'TimeStamp' column to a 'Time' column.
4. Using the 'Replace Date Time' rule process the 'Date' column from Unix to 'yyyy-MM-dd'.
5. Using the 'Replace Date Time' rule process the 'Date' column from Unix to 'HH:mm'.
6. Using the 'Delete Column' rule remove the 'TimeStamp' column.

Select the 'TimeStamp' Column

From the 'Palette', under 'Column Selectors', click 'Column List'. We create a 'Column Selector' for the 'TimeStamp' column so we can use it later.

Copy the 'TimeStamp' Column

From the 'Palette', under 'Column Processors', click 'Copy Column'. We are going to create two rules to copy the 'TimeStamp' column, one for the 'Date' and one for the 'Time'.

When we have done that we will also need to create two 'Column Selector's for the 'Date' and 'Time' columns so we can use them later

Convert the 'Date' and 'Time' Columns

From the 'Palette', under 'Cell Processors', click 'Replace Date Time'. We are going to create two rules to convert the 'Date'and 'Time' columns using the format 'yyyy-MM-dd', and 'HH:mm' respectively.

Delete the 'TimeStamp' Column

From the 'Palette', under 'Column Processors', click 'Delete Column'. Now we are done we can delete the 'TimeStamp' column.

Note: During processing the copy and delete actions on the columns are not executed on a column basis, but on a row basis, which is much more efficient than it might appear at first.

Process the Job

Click the 'Home/Process' job button to start the Job processing the source to the output.

Use the Preview

During the development of the Cleaner it can be easier to use the Preview ('Home/Show Job Preview') to see previews of the raw data from the Job source and the effects of the Cleaner on the output.

Attachments