PostgreSQL

In PostgreSQL, DATE functions are used to manipulate and query date values. These functions are crucial for working with date and time data effectively. They allow you to perform operations such as calculating differences between dates, extracting specific components of dates, and formatting dates in various ways. For beginners, understanding these functions can simplify many common tasks involving date data.

Here are some key DATE functions in PostgreSQL:

1. CURRENT_DATE and CURRENT_TIMESTAMP
   • CURRENT_DATE returns the current date.
   • CURRENT_TIMESTAMP returns the current date and time.

   SELECT CURRENT_DATE;
   SELECT CURRENT_TIMESTAMP;
   

2. DATE_TRUNC
   • DATE_TRUNC truncates a date or time to a specified level of precision, such as year, month, or day.

   SELECT DATE_TRUNC('month', CURRENT_DATE) AS start_of_month;
   SELECT DATE_TRUNC('year', CURRENT_DATE) AS start_of_year;
   

3. AGE
   • AGE calculates the interval between two dates or between a date and the current date.

   SELECT AGE(CURRENT_DATE, '2000-01-01') AS age_of_date;
   

4. DATE_PART
   • DATE_PART extracts a specific part (like year, month, or day) from a date.

   SELECT DATE_PART('year', CURRENT_DATE) AS current_year;
   SELECT DATE_PART('month', CURRENT_DATE) AS current_month;
   

5. TO_CHAR
   • TO_CHAR formats a date or time value into a string according to a specified format.

   SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS formatted_date;
   SELECT TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD Month YYYY') AS formatted_timestamp;
   

6. Example Usage with Banking Tables
   • Suppose you have a table transactions with a transaction_date column. You can use these functions to query and format date-related data.

   -- Find transactions from the current month
   SELECT *
   FROM transactions
   WHERE DATE_TRUNC('month', transaction_date) = DATE_TRUNC('month', CURRENT_DATE);
   
   -- Calculate age of transactions
   SELECT transaction_id, AGE(CURRENT_DATE, transaction_date) AS transaction_age
   FROM transactions;
   

   These functions can help you effectively manage and analyze date data in PostgreSQL, making it easier to handle various date-related tasks.

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
    count(*) as monthly_order_count
FROM act_order
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

SELECT EXTRACT( HOUR FROM order_date)
    count(*) as order_count
FROM act_order
GROUP BY EXTRACT( HOUR FROM order_date)
ORDER BY count(*) DESC
LIMIT 5;

SELECT EXTRACT(YEAR FROM CURRENT_DATE) - birth_year as age
    count(*) as customer_count
FROM org_client
WHERE gender= 'M'
GROUP BY EXTRACT(YEAR FROM CURRENT_DATE) - birth_year
ORDER BY EXTRACT(YEAR FROM CURRENT_DATE) - birth_year DESC
LIMIT 3;