Amazon Redshift database is a platform that allows us to store our data on the cloud. With the advancement in science and technology now we need to store timestamp with other data to keep a record of everything. Timestamp has various formats; one format is Unix epoch which denotes the number of seconds that have been passed since January 1, 1970, at 00:00:00 UTC. That is not human readable to convert epoch to human readable form we convert to date time format. The datetime format is commonly used for data analysis and processing.
In this article, we will convert epoch timestamp to datetime format and after this article, you will be able to convert epoch timestamp to datetime format with great efficiency.
Syntax for conversion:
To convert from epoch to datetime format in Amazon Redshift there is a built-in method. for datetime conversion follow the below syntax.
# Syntax for conversion
SELECT TIMESTAMP 'epoch' + * INTERVAL '1 second';
The above syntax has three parts defined as under.
TIMESTAMP ‘epoch’
This part of the conversion represents the Unix epoch which denotes the number of seconds that have been passed since January 1, 1970, at 00:00:00 UTC.
<epoch value>
This will contain the epoch timestamp, don’t forget to change this placeholder with the timestamp you want to change.
INTERVAL
This interval will multiply the epoch value with the defined interval if you set an interval of 1 second. It will convert epoch timestamp to datetime format.
Create Redis Table
Code:
# creating table
CREATE TABLE Students (
s_id integer not null,
s_name VARCHAR (100) not null,
s_address VARCHAR (200) not null,
s_email VARCHAR (300) not null,
s_grade VARCHAR (200) not null,
s_date DATE not null,
s_height REAL not null
);
Insert Values to Table
Code:
# inserting values into the table
INSERT INTO Students VALUES
(236, 'Hira', 'Rawalpindi Mess', '[email protected]', 'Graduate', '1631256634', 5.5),
(237, 'Uzair', 'Rawalpindi', '[email protected]', '12', '1620715834', 5.6),
(238, 'T Hira', 'Multan Mess', '[email protected]', 'Graduate', '1559371834', 5.6),
(239, 'Tassadaq','Rawat main', '[email protected]', 'Undergraduate', '1554526234', 5.3),
(240, 'Samra', 'Sparco main', '[email protected]', 'Masters', '1554537034', 5.2);
Get Data from the Table
# table structure
SELECT * FROM "dev"."public"."students";
Convert current epoch time to datetime format.
In Amazon Redshift we can convert epoch time to datetime format. Let’s understand the conversion method with the help of an example. The below query will convert the epoch timestamp 1694323088 to the human-readable timestamp format which is the datetime format. TIMESTAMP tells the database that we are taking the epoch time then you give the epoch value for conversion. Multiply that value with the interval here the interval is 1 second. The:: INTERVAL AS “Current TimeDate” is used to name the column. The resulting output will display the converted time and the name of the column will be in lowercase by default Amazon Redshift display column name in lowercase.
Code:
# Converting the current epoch time to datetime format.
SELECT TIMESTAMP 'epoch' + 1694323088 * INTERVAL '1 second'
::INTERVAL AS "Current TimeDate";
Output:
Convert epoch timestamp to datetime in students table
This example converts the students table column s_date which contains epoch timestamp to datetime format. Select will select the columns s_id, s_name, s_email, and then TIMESTAMP epoch. After that s_date is the epoch value, multiply this value by the interval of 1 second and display this converted time in a column named s_datetime.
Code:
# Converting the epoch timestamp to datetime in students table
SELECT s_id, s_name, s_email, TIMESTAMP 'epoch' + s_date * INTERVAL '1 second' AS s_dateTime
FROM students;
Output:
Conclusion:
This article explains the conversion of epoch timestamp to datetime format. We humans cannot remember and understand the epoch format nevertheless in science, technology, and programming we use epoch timestamps for data analysis and processing. For human understanding, we convert epoch timestamp to datetime format. Amazon Redshift facilitates this conversion by providing a built-in function. This article helps you in understanding the concept and conversion method from epoch to datetime in Amazon Redshift.