Using Date and Time Types in Postgres

When working with dates and time in PostgreSQL, understanding the available date/time types is useful. As a Postgres user, let’s explore these types in more detail.

1. Date Data Type

• Date: Represents a date value (e.g., birthdays, deadlines).

  • Stored as 4 bytes.

  • Range: 4713 BC to 5874897 AD.

  • Example:

    CREATE TABLE events (
        event_id serial PRIMARY KEY,
        event_date date
    );
    
    INSERT INTO events (event_date) VALUES ('2024-07-10');
    
    

2. Time Data Type

• Time: Represents a time-of-day value (e.g., appointment times).

  • Stored as 8 bytes.

  • Range: 00:00:00 to 24:00:00.

  • Example:

    CREATE TABLE appointments (
        appt_id serial PRIMARY KEY,
        appt_time time
    );
    
    INSERT INTO appointments (appt_time) VALUES ('14:30:00');
    
    

3. Timestamp Data Type

• Timestamp: Represents a combined date and time value (e.g., event timestamps).

  • Stored as 8 bytes.

  • Range: 4713 BC to 294276 AD.

  • Example:

    CREATE TABLE log_entries (
        log_id serial PRIMARY KEY,
        log_timestamp timestamp
    );
    
    INSERT INTO log_entries (log_timestamp) VALUES ('2024-07-10 15:45:00');
    
    

4. Timestamp with Time Zone Data Type

• Timestamp with Time Zone (timestamptz): Includes time zone information.

  • Stored as 8 bytes.

  • Example:

    CREATE TABLE meetings (
        meeting_id serial PRIMARY KEY,
        meeting_time timestamptz
    );
    
    INSERT INTO meetings (meeting_time) VALUES ('2024-07-10 10:00:00-07');
    
    

5. Interval Data Type

• Interval: Represents a duration of time (e.g., event duration).

  • Stored as 16 bytes.

  • Example:

    CREATE TABLE tasks (
        task_id serial PRIMARY KEY,
        task_duration interval
    );
    
    INSERT INTO tasks (task_duration) VALUES ('2 days 3 hours');
    
    

Remember to adapt these examples to your specific use case. Happy querying!

For more detailed information, consult the official PostgreSQL documentation. If you have further questions, feel free to ask us on our Discord!