Postgres max timestamp

by

Postgres max timestamp

In PostgreSQL, you can use the MAX function along with the timestamp data type to get the maximum or latest timestamp value from a column in a table.

Syntax:

    SELECT MAX(column_name) FROM table_name;

Example 1:

Let’s say we have a table called “orders” with a column “order_date” which stores the timestamp when an order was placed. We want to find the latest order’s timestamp. 

    CREATE TABLE orders (
      id serial primary key,
      order_date timestamp
    );
    
    INSERT INTO orders (order_date) VALUES 
      ('2021-01-01 10:00:00'),
      ('2021-01-02 09:30:00'),
      ('2021-01-03 15:45:00');
    
    SELECT MAX(order_date) FROM orders;

The result of the above query will be ‘2021-01-03 15:45:00’, which is the maximum or latest timestamp value from the “order_date” column.

Example 2:

You can also use the MAX function with multiple columns to get the maximum timestamp based on another column’s value. Let’s say we have a table called “logs” with columns “id”, “log_date”, and “severity”. We want to find the latest log’s timestamp where the severity is ‘ERROR’. 

    CREATE TABLE logs (
      id serial primary key,
      log_date timestamp,
      severity varchar(10)
    );
    
    INSERT INTO logs (log_date, severity) VALUES 
      ('2021-01-01 10:00:00', 'INFO'),
      ('2021-01-02 09:30:00', 'DEBUG'),
      ('2021-01-03 15:45:00', 'ERROR'),
      ('2021-01-04 12:00:00', 'ERROR');
      
    SELECT MAX(log_date) FROM logs WHERE severity = 'ERROR';

The result of the above query will be ‘2021-01-04 12:00:00’, which is the maximum or latest timestamp value from the “log_date” column where the severity is ‘ERROR’.

Leave a comment