How to Read Timestamp Data in Database

Introduction to SQL Timestamp

Timestamp is a information type and function in Standard Structured Query Language (SQL) that lets the states store and work with both date and time data values, usually without specified time zones. The timestamp has a storage size of 8 bytes that tin have date and time values ranging from 4713 BC and 294276 Advertising and provides a resolution of 1 microsecond or 14 digits. Some SQL databases allow for customization of a timestamp information type where we tin can specify a timezone for the timestamp then that every time the database is used in a unlike timezone, information technology shows and accepts the respective date and time. For example, in PostgreSQL, nosotros have a "timestamptz" data type that takes intendance of time zone changes as well. Timestamptz data type also has a storage size similar to a timestamp of 8 bytes that can take date and time values ranging from 4713 BC and 294276 AD and provides a resolution of 1 microsecond or 14 digits.

In this article, nosotros 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 Advertisement.

Timestamptz 'date_expression time_expression +/- time_zone_expression ';
2004-10-19 10:23:54;

In this case, a valid timestamp data expression consists of a appointment 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

Beneath are a few examples to sympathise SQL Timestamp information type:

Note: For the purpose of this commodity, we accept used the PostgreSQL server.

Example #one

Getting to know important details of timestamp and timestamptz data type in SQL.

Code:

SELECT typname, typlen, typtype, typcategory
FROM pg_type
WHERE typname ~ '^timestamp';

Example #2

SQL query to illustrate the difference betwixt timestamp and timestamptz data types.

Code:

SELECT TIMESTAMP '2020-03-23 00:00';

SELECT TIMESTAMPTZ '2020-03-23 00:00';

Example #three

Some examples to show functions related to timestamp data type in SQL. First, allow's commencement by converting or casting a given appointment into a timestamp format, as shown beneath.

Code:

SELECT '2020-03-23' :: timestamptz;

Suppose if nosotros desire to know the electric current timestamp, nosotros tin use the current_timestamp function as shown below. It will return the electric current timestamp, i.e. current date and fourth dimension implicitly converted into the default timezone.

SELECT current_timestamp;

Further, we can also check the timezone we are currently working in past using the Evidence timezone statement beneath. This becomes very helpful when yous accept operations all over the globe.

SHOW timezone;

Next, nosotros can even change the electric current timezone to a different timezone using the Set up timezone statement. Here, y'all may choose from a wide multifariousness of timezones similar 'Asia/Kolkata', 'Europe/Zurich', 'US/Pacific' etc. In this example, nosotros have changed the timezone from 'Asia/Kolkata' to 'US/Pacific'.

Fix timezone = 'US/Pacific';

A few functions like EXTRACT in SQL let u.s.a. extract a specific piece of data from the timestamp. For example, we can extract 24-hour interval, Calendar month, YEAR, Hour, MINUTE, SECONDS, etc., from the timestamp.

In the post-obit examples, we accept tried to extract DAY and MONTH from the timestamp.

SELECT Excerpt(DAY FROM '2020-03-23 00:00':: timestamp);

SELECT EXTRACT(MONTH FROM '2020-03-23 00:00':: timestamp);

Instance #4

SQL query to generate a series of timestamps in a specified timezone. In this example, we have generated a serial of timestamps with a regular interval of 5 hours in two time zones, namely, 'Indian Standard Time' and 'Pacific Standard Time. We tin can discover the deviation betwixt the ii serial by closely looking at the outputs.

Code:

SELECT generate_series(
timezone('IST', '2020-03-23 00:00'::timestamp),
timezone('IST', '2020-03-23 23:00'::timestamp),
'5 60 minutes'::interval
);

SELECT generate_series(
timezone('PST', '2020-03-23 00:00'::timestamp),
timezone('PST', '2020-03-23 23:00'::timestamp),
'5 hr'::interval
);

Example #5

A applied example to illustrate the utilize of timestamp or timestamptz data type in SQL. Let'due south 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. So the customers' table tin can be created something like this :

Code:

CREATE Tabular array Customers (
customer_id int,
customer_name varchar(255),
city varchar(255),
date_of_birth timestamptz
);

Subsequently inserting the relevant information, the data in the tabular array will look something like this:

Suppose we desire to wish our customers in London who take their birthdays today i.e. '24th March 2020' as per Indian Standard Time; we might exist required to write a SQL query equally shown beneath:

SELECT customer_name, date_of_birth
FROM customers
WHERE Excerpt(Day FROM date_of_birth) = '24'
AND EXTRACT(MONTH FROM date_of_birth) = '03';

Merely in the above example, nosotros tin see that since the customer is from London and it's non 24th March in that location, the commencement query might not be the best option. And then we can meliorate the query past mentioning the time zone equally 'GMT', as mentioned in the query below.

SELECT customer_name, date_of_birth
FROM customers
WHERE Excerpt(DAY FROM date_of_birth at time zone 'GMT') = '24'
AND EXTRACT(MONTH FROM date_of_birth at time zone 'GMT') = '03';

Conclusion

SQL timestamp is a information blazon and role 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 take operations all over the globe.

Recommended Manufactures

This is a guide to SQL Timestamp. Here nosotros hash out the Introduction to SQL Timestamp and the applied examples and unlike subquery expressions. You can also become through our suggested manufactures to learn more than –

1. Top v Examples of SQL Server Substring
2. Overview of Triggers in SQL
3. What is SQL Subquery?
4. Introduction to SQL Arithmetic Operators
5. ANY in SQL | Examples | Syntax

How to Read Timestamp Data in Database

Source: https://www.educba.com/sql-timestamp/