Converting Timestamps and Transforming Data Using Bold Data Hub

In this article, we will demonstrate how to import tables from a CSV file, convert timestamps through transformations, and migrate the cleaned data into the destination database using Bold Data Hub. Follow the step-by-step process below.

Sample Data Source:

Sample CSC Data

Creating Pipeline

Learn about Pipeline Creation

Applying Transformation

Learn more about transformation here

Converting Timestamps

Overview

Raw timestamps are often stored in UNIX or numeric formats, making them difficult to interpret. Converting them into readable formats and adjusting them to the correct time zone improves data clarity and usability.

Approach

We use TO_TIMESTAMP to convert raw numeric timestamps into a readable datetime format and apply a time zone conversion to Asia/Kolkata.

SQL Query for Converting Timestamps

SELECT 
    Ticket_ID, 
    TO_TIMESTAMP(CAST(Ticket_Allocation_Timestamp AS BIGINT)) 
    AT TIME ZONE 'Asia/Kolkata' AS Ticket_Allocation_DateTime_Asia 
FROM {pipeline_name}.sample_csc_data;