Introduction to SQL Timestamp
Timestamp is a data type as well as function in Standard Structured Query Language (SQL) that lets us store and work with both date and time data values usually without time zones specified. Timestamp has a storage size of 8 bytes that can accept date and time values ranging from 4713 BC and 294276 AD and provides a resolution of 1 microsecond or 14 digits. Some SQL databases allow for customization of timestamp data type where we can specify a timezone for the timestamp, so that every time the database is used in a different timezone, it shows and accepts the corresponding date and time. For example, in PostgreSQL, we have a “timestamptz” data type that takes care of time zone changes as well. Timestamptz data type also has a storage size similar to a timestamp of 8 bytes that can accept date and time values ranging from 4713 BC and 294276 AD and provides a resolution of 1 microsecond or 14 digits.
In this article, we will be learning the functions and differences of “timestamp” and “timestamptz” data types with the help of a few examples.
Syntax and Parameters:
The basic syntax of “timestamp” data type in SQL is as follows :
Timestamp ‘date_expression time_expression’;
A valid timestamp data expression consists of a date and a time, followed by an optional BC or AD.
Timestamptz ‘date_expression time_expression +/- time_zone_expression ’;
In this case, a valid timestamp data expression consists of a date and a time, followed by a time_zone expression concatenated with a ‘+/-’ sign based on the position of the time zone with respect to ‘GMT’ and finally followed by an optional BC or AD.
Examples to Implement of SQL Timestamp
Below are a few examples to understand SQL Timestamp data type:
Getting to know important details of timestamp and timestamptz data type in SQL.
SELECT typname, typlen, typtype, typcategory
WHERE typname ~ '^timestamp';
SQL query to illustrate the difference between timestamp and timestamptz data types.
SELECT TIMESTAMP '2020-03-23 00:00';
SELECT TIMESTAMPTZ '2020-03-23 00:00';
Some examples to show functions related to timestamp data type in SQL. Let’s start by converting or casting a given date into a timestamp format as shown below.
SELECT '2020-03-23' :: timestamptz;
Suppose if we want to know the current timestamp, we can use current_timestamp function as shown below. It will return the current timestamp, i.e. current date and time implicitly converted into default timezone.
Further, we can also check what timezone we are currently working in by using the SHOW timezone statement as shown below. This becomes very helpful when you have operations all over the world.
Next, we can even change the current timezone to a different timezone using the SET timezone statement. Here, you may choose from a wide variety of timezones like ‘Asia/Kolkata’, ‘Europe/Zurich’, ‘US/Pacific’ etc. In this example, we have changed the timezone from ‘Asia/Kolkata’ to ‘US/Pacific’.
SET timezone = 'US/Pacific';
There are a few functions like EXTRACT in SQL which lets us extract a specific piece of information from the timestamp. We can extract DAY, MONTH, YEAR, HOUR, MINUTE, SECONDS, etc. from the timestamp.
In the following examples, we have tried to extract DAY and MONTH from the timestamp.
SELECT EXTRACT(DAY FROM '2020-03-23 00:00':: timestamp);
SELECT EXTRACT(MONTH FROM '2020-03-23 00:00':: timestamp);
SQL query to generate a series of timestamps in a specified timezone. In this example, we have generated a series of timestamps with a regular interval of 5 hours in two time zones, namely, ‘Indian Standard Time’ and ‘Pacific Standard Time’. We can observe the difference between the two series by closely looking at the outputs.
timezone('IST', '2020-03-23 00:00'::timestamp),
timezone('IST', '2020-03-23 23:00'::timestamp),
timezone('PST', '2020-03-23 00:00'::timestamp),
timezone('PST', '2020-03-23 23:00'::timestamp),
A practical example to illustrate the use of timestamp or timestamptz data type in SQL. Let’s take an example of a business case, where a multinational bank wants to wish its customers “Happy Birthday” based on the customer’s local time. The bank’s database has a list of customers with their date of births. The customers’ table can be created something like this :
CREATE TABLE Customers (
After inserting the relevant information, the data in the table will look something like this:
Suppose we want to wish our customers in London who have their birthdays today i.e ‘24th March 2020’ as per Indian Standard Time, we might be required to write a SQL query as shown below:
SELECT customer_name, date_of_birth
WHERE EXTRACT(DAY FROM date_of_birth) = '24'
AND EXTRACT(MONTH FROM date_of_birth) = '03';
But in the above example, we can see that since the customer is from London and its not 24th March there, the first query might not be the best option. We can improve the query by mentioning the time zone as ‘GMT’, as mentioned in the query below.
SELECT customer_name, date_of_birth
WHERE EXTRACT(DAY FROM date_of_birth at time zone 'GMT') = '24'
AND EXTRACT(MONTH FROM date_of_birth at time zone 'GMT') = '03';
SQL timestamp is a data type and a function that is used to store and work with data values in date and time formats, sometimes along with time zones and AD/BCs. They are very helpful when we have operations all over the world.
This is a guide to SQL Timestamp. Here we discuss the Introduction to SQL Timestamp and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –