WHERE EXTRACT(DAY FROM date_of_birth at time zone 'GMT') = '24'ĪND EXTRACT(MONTH FROM date_of_birth at time zone 'GMT') = '03' So we can improve the query by mentioning the time zone as ‘GMT’, as mentioned in the query below. WHERE EXTRACT(DAY FROM date_of_birth) = '24'ĪND EXTRACT(MONTH FROM date_of_birth) = '03' īut in the above example, we can see that since the customer is from London and it’s not 24th March there, the first query might not be the best option. ‘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 Suppose we want to wish our customers in London who have their birthdays today i.e. So the customers’ table can be created something like this :Īfter inserting the relevant information, the data in the table will look something like this: The bank’s database has a list of customers with their date of births. 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. We can observe the difference between the two series by closely looking at the outputs.Ī practical example to illustrate the use of timestamp or timestamptz data type in SQL. 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. SQL query to generate a series of timestamps in a specified timezone. SELECT EXTRACT(MONTH FROM ' 00:00':: timestamp) SELECT EXTRACT(DAY FROM ' 00:00':: timestamp) In the following examples, we have tried to extract DAY and MONTH from the timestamp. For example, we can extract DAY, MONTH, YEAR, HOUR, MINUTE, SECONDS, etc., from the timestamp. SET timezone = 'US/Pacific' Ī few functions like EXTRACT in SQL let us extract a specific piece of information from the timestamp. In this example, we have changed the timezone from ‘Asia/Kolkata’ to ‘US/Pacific’. Here, you may choose from a wide variety of timezones like ‘Asia/Kolkata’, ‘Europe/Zurich’, ‘US/Pacific’ etc. Next, we can even change the current timezone to a different timezone using the SET timezone statement. This becomes very helpful when you have operations all over the world. SELECT current_timestamp įurther, we can also check the timezone we are currently working in by using the SHOW timezone statement below. current date and time implicitly converted into the default timezone. It will return the current timestamp, i.e. Suppose if we want to know the current timestamp, we can use the current_timestamp function as shown below. First, let’s start by converting or casting a given date into a timestamp format, as shown below. Some examples to show functions related to timestamp data type in SQL. SQL query to illustrate the difference between timestamp and timestamptz data types. Getting to know important details of timestamp and timestamptz data type in SQL.Ĭode: SELECT typname, typlen, typtype, typcategory Note: For the purpose of this article, we have used the PostgreSQL server.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |