Autumn Offers - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Dealing with dates
Working with time
Date and time data types in PostgreSQL
Extracting dates and times
Timezone conversion
Format date and time
Current date and time data
Summary
47. Summary

Instruction

Fantastic job! It's time to review what we know about date and time in PostgreSQL:

  • PostgreSQL uses dates ('2010-01-01'), times ('13:00:00'), and timestamps ('2010-01-01 13:00:00').
  • You can compare the above types with BETWEEN or basic operators like: <,<=,>,>=.
  • You can specify the resulting order with ORDER BY.
  • The TIMESTAMP data type allows you to save date and time data in your local time zone.
  • To get parts of a date or time, use the EXTRACT() or DATE_PART() functions.
  • Use the DATE_TRUNC(x, y) function to truncate the date and time (passed as y) to the given date or time part (passed as x).
  • Use the INTERVAL operator to add or subtract a period of date or time.
  • Use the operator :: to convert data types.
  • Use the AGE(x, y) function to return the difference between the current date or between two dates, x and y, specified as arguments.
  • Use converted time zones with AT TIME ZONE 'new_timezone'.
  • Use TO_CHAR(timestamp, format) to display the date in a given format.
  • To get the current date and time, use:
    • CURRENT_TIMESTAMP to get the timestamp with the time zone (or CURRENT_TIMESTAMP(p) where p is the precision of fractional seconds).
    • NOW() to get date and time with time zone.
    • CURRENT_DATE to get only the date.
    • CURRENT_TIME to get only the time with the time zone (or CURRENT_TIME(p) where p is the precision of fractional seconds).

Are you ready to practice what you've learned?

Exercise

Click Next exercise to continue.