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
18. Date and time data types in PostgreSQL
Extracting dates and times
Timezone conversion
Format date and time
Current date and time data
Summary

Instruction

Good! Did you notice the format? Here it is again:

2014-06-10 07:55:00.125

Let's analyze it. In PostgreSQL the date and time data type is timestamp. It contains both date and time parts. The first part is the date and the second part is the time, reported to fractions of a second. The fractional value in both date and time data types, if given, must be in the range of 0 to 6 digits. The default precision for these data types is 6 digits.

The supported range in timestamp is '4713-01-01 BC' to '294276-12-31 AD'. Note that the upper value for timestamp is different than for date (which is '5874897-12-31 AD').

timestamp can also contain time zone:

'2018-05-23 07:55:12.534211+02'

The '+02' refers to the timezone – in this case it means that we are two hours ahead of UTC. Time zone is optional. The timestamp data type without time zone stores date and time in the local time of the database server (in our database it is UTC).

Now, let's look at a query:

SELECT id
FROM aircraft
WHERE withdrawn_timestamp < launched_timestamp;

The above query checks for typos in our table; we can make sure no aircraft was removed from service before it was launched.

Exercise

Run the example query and find out for yourself if there are errors in the aircraft table.