Updated March 8, 2023
Introduction to Redshift timestamp
Redshift timestamp is used to store our date type column in date and time format, it will save our date in timestamp without timezone format. Timestamp is nothing but the alias of timestamp without timezone, after using timestamp datatype to our column it will store the date in date and time format, it will not storing the timezone. We can store the timezone using timestamptz datatype in redshift, using timestamp data type we are only storing the complete date and time of the day. Storage size of timestamp datatype in redshift is 8 bytes.
Syntax:
Below is the syntax of timestamp datatype in redshift is as follows.
- Assign timestamp datatype to the column at the time of table creation –
Create table name_of_table (column_name1 datatype, column_name2 datatype, column_name3 timestamp, ….., column_nameN timestamp);
- Assign the datatype as timestamp at the time adding new column to the table –
ALTER TABLE name_of_table ADD COLUMN name_of_column timestamp;
- Change the datatype of a column as timestamp –
ALTER TABLE name_of_table ALTER COLUMN name_of_column type timestamp;
- Check the current date and timestamp –
- Select now () :: timestamp; — Showing current date and time
- Select CURRENT_TIMESTAMP :: timestamp; — It will also showing the current date and time.
Parameter description syntax of timestamp in redshift.
- Create table – This command is used to create the table in redshift using this command we have assigning the timestamp data type to the newly created column. We can assign the timestamp datatype to one or multiple columns in redshift.
- Table name – This is the name of the table on which column we have assigned the data type as a timestamp.
- Column name – This is nothing but the name of the column on which we have defined the data type as timestamp. We can also assign different datatype to the different columns.
- Datatype – This is defined as datatype which was we have to assign to the column at the time of table creation. We can assign data type as per the data which was we have loaded into the table.
- Alter table – This command is used to alter the table to add and change the data type of column. We can change the column datatype as a timestamp by using alter table command.
- Add column – This command is used to add a column in redshift. After adding a new column to the table we have assigned the datatype as a timestamp.
- Timestamp – This is the datatype that was used in redshift to store the date in date and time format. Timestamp will not store the timezone.
How does timestamp work in Redshift?
- The range of timestamp datatype in redshift is 4713 BC to 294276 AD, also the storage size of timestamp data type is 8 bytes.
- Storage size of timestamp and timestamptz (timestamp with timezone) datatype is the same as 8 bytes.
- Timestamp datatype in redshift will stores the maximum values up to the precision of six digits in fractional seconds.
- At the time of insertion date into the timestamp datatype column, the value of date will be implicitly converted into the fully timestamp value.
- The fully timestamp values contains default value as (00) for missing seconds, minutes, and hours. Time zone values are ignored from the input value.
- The below example shows that date is implicitly converted into timestamp value if the column contains the datatype as timestamp.
- In the first example we have inserted date as ’12-08-2021′, we can see that it will not contains any timestamp, but our date column contains the datatype as timestamp so it will implicitly convert date in date and time format.
- We can see the result of ’12-08-2021′ date using timestamp datatype is “2021-12-08 00:00:00”.
Code –
Insert into redshift_timestamp values (1, '12-08-2021');
Select * from redshift_timestamp where id = 1;
- In the above result, we can see that hour, minutes and second’s value implicitly comes in the format as “00”.
- Below are the timestamp function available in redshift are as follows.
- TIMESTAMP_CMP function.
- TIMESTAMP_CMP_DATE function.
- TIMESTAMP_CMP_TIMESTAMPTZ function.
- Timestamp compare function is used to compare the value of two timestamps and returns the value in integer format.
- If suppose our timestamp value is identical then timestamp compare function will return the values as zero.
- The second function timestamp compare date is used to compare the timestamp with date value.
- If suppose our timestamp and date value are identical then this function will return the values as zero.
Examples
Below is the example of the redshift timestamp is as follows.
- Create table using datatype as timestamp –
- The below example shows that create a table using datatype as the timestamp is as follows. We have to create the table name as timestamp_date and on end_date column we have to assign the datatype as a timestamp.
Code –
Create table timestamp_date (id int, start_date date, end_date timestamp);
Insert value into the timestamp datatype column –
- The below example shows that insert values into the timestamp datatype column are as follows.
- In the first example, we have inserted timestamp values as “2020-10-05 19:10:25-07”. In the second example we have inserted the timestamp value as “12-08-2021”.
- In the first example, we have used date with time value but in the second example we have used only date value.
Code
Insert into timestamp_date values (1, 'Jun 1, 2021', '2020-10-05 19:10:25-07');
Insert into timestamp_date values (1, 'Jun 1, 2021', '12-08-2021');
Select * from timestamp_date;
Add new column into table using datatype as timestamp –
- The below example shows that add new column into the table using datatype as timestamp is as follows.
- In the below example, we have added column name as start_date1 and assign datatype as a timestamp.
Code –
Alter table timestamp_date ADD column start_date1 timestamp;
Select current date and time using timestamp –
- The below example shows select current date and time by using timestamp are as follows.
- In the first example, we have selecting date by using the now function and in the second example we have selecting date with current_timestamp function.
Code –
Select now () :: timestamp;
Select CURRENT_TIMESTAMP :: timestamp;
Conclusion
Timestamp is the alias of timestamp with timezone in redhat. Basically, timestamp and timestamptz are two different datatype used to store the date in redshift. Timestamp is very useful and important to store the date in date with time format. There are multiple timestamp functions available in redshift.
Recommended Articles
This is a guide to Redshift timestamp. Here we discuss How does timestamp work in Redshift along with the examples. You may also have a look at the following articles to learn more –
