_edited_edited.png)
Hello Readers,
Date is a particular month, day, and year at which some event happened or will happen.
Time is the continued sequence of existence and events that occurs in an apparently irreversible succession from the past, through the present, and into the future. General time or all time refers to linguistic time concept that covers past time, present time and future time together, as one single concept.
In this article, we will explore key functions like make_date, make_time, make_timestamp, and more.
Create a date:
Make_date is a function which is used to create a date from provided year, month and day values.
Syntax:
make_date(year int, month int, day int)->date
Parameters:
year : year is an integer.
month : month is an integer (1 for January-12 for December).
day : day is an integer (1-31).
Example:
SELECT make_date(2024, 9,21);
This example creates a specific date using make_date function. It takes 2024 as year, 9 as month and 21 as day. When query is executed, date created in the format of yyyy-mm-dd (2024-09-21).
Create a time:
In PostgreSQL, the make_time function is used to create a time value from given hour, minute, and second components. This function is useful for creating and managing time values in databases.
Syntax:
make_time(hour int, min int, sec double precision)-> time
Parameters:
hour: hour is an integer (0 to 23).
min: minute is a integer (0 to 59).
sec: seconds is a double precision (can include fractions).
Example:
SELECT make_time(02, 50, 25.50);
This query creates a time value of 2 hours, 50 minutes, and 25.50 seconds. when query executed, the time will be generated in the Data OutPut window.
Create an interval using function:
Make_interval function is used to create an interval value from specified units of time.
Syntax:
make_interval([years int [, months int [, weeks int [, days int [,hours int [, mins int [,secs double precision ]]]]]]]->interval
Parameters:
year: year is an integer.
month:month is an integer (1 for January-12 for December).
days:day is an integer (1-31).
hour: hour is an integer (0 to 23).
min: minute is a integer (0 to 59).
sec: seconds is a double precision (can include fractions).
we can create a different combination of interval in date filed using make_interval function .
Simple interval Example:
SELECT make_interval(years => 10, months => 6) AS simple_interval;
In this example, make_interval takes the number 10 as years and number 6 as months input.The result is labeled "simple_interval," making it easy to identify. It create a time period of ten years and six months which is displayed in the Data Output window.
Full Interval Example:
SELECT make_interval(years => 10, months => 6, weeks => 2, days => 7, hours => 2, mins => 50, secs => 25.50) AS full_interval;
This query creates a time interval that combines several time components. It specifies:10 years,6 months,2 weeks,7 days,2 hours,50 minutes,25.5 seconds. The output should be as present in this image .
Adding Interval Example:
SELECT make_interval(months=>10, days => 7) + make_interval(days=> 2) AS Adding_interval;
The query adds these two intervals together, resulting in a new interval that includes 10 months and 9 days (since 7 days plus 2 days equals 9 days).
Subtracting Interval Example:
SELECT make_interval(months=>10, days => 7) - make_interval(days=> 2) AS Subtract_interval;
The query subtracts the second interval (2 days) from the first interval (10 months and 7 days), resulting in a new interval that includes 10 months and 5 days (since 7 days minus 2 days equals 5 days).
Create Timestamp:
In PostgreSQL, make_timestamp function creates a timestamp from specified year, month, day, hour, minute, and second values.
Syntax:
make_timestamp ( year int, month int, day int, hour int, min int, sec double precision )->timestamp
Parameters:
year: year is an integer.
month:month is an integer (1 for January-12 for December).
days:day is an integer (1-31).
hour: hour is an integer (0 to 23).
min: minute is a integer (0 to 59).
sec: seconds is a double precision (can include fractions).
Example:
SELECT make_timestamp(2024, 10, 21, 2, 50, 25.5) AS new_timestamp;
This query makes a timestamp for October 21, 2024, at 2:50 and 25.5 seconds. It shows a specific date and time, which is useful for keeping track of events.
Create Timestampz:
The make_timestamptz function is used to create a timestamp that includes the time zone.
Syntax:
make_timestamptz ( year int, month int, day int, hour int, min int, sec double precision [, timezone text ] ) -> timestamp with time zone.
Parameters:
year: year is an integer.
month:month is an integer (1 for January-12 for December).
days:day is an integer (1-31)
hour: hour is an integer (0 to 23).
min: minute is a integer(0 to 59).
sec: seconds is a double precision (can include fractions).
timezone: (optional) A text string for the time zone (e.g., 'EST').
Example:
SELECT make_timestamptz(2024, 10, 21, 2, 50, 25.5, 'EST') AS new_timestamptz;
This query makes a timestamp for October 21, 2024, at 2:50 and 25.5 seconds in the Eastern Standard Time (EST) zone. When you run it, you'll get the exact date and time with the EST label. This helps you keep track of events happening in that time zone clearly and accurately.
In conclusion, understanding how to use PostgreSQL's date and time functions—like make_date, make_time, and make_timestamp—is really important for managing time-related data. These functions make it simple to create and handle dates and times in the database. By using them, you can ensure your data stays accurate and make tasks like scheduling events or calculating dates much easier. Overall, mastering these functions will help you work more effectively with your database.
Comments