Date, time, and timestamp types in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What data type in PostgreSQL stores only the date without time?

The DATE type stores only the date (year, month, day) without any time information.

beginner

Which PostgreSQL data type stores time of day without a date?

The TIME type stores the time of day (hours, minutes, seconds) without any date.

intermediate

What is the difference between TIMESTAMP and TIMESTAMPTZ in PostgreSQL?

TIMESTAMP stores date and time without time zone information.<br>TIMESTAMPTZ stores date and time with time zone awareness, automatically converting to UTC internally.

intermediate

How does PostgreSQL handle time zones with the TIMESTAMPTZ type?

PostgreSQL converts TIMESTAMPTZ values to UTC internally and adjusts them to the client's time zone on retrieval, ensuring correct local time display.

beginner

What format does PostgreSQL use to input and output DATE and TIME types?

PostgreSQL uses ISO 8601 format by default:<br>- DATE: 'YYYY-MM-DD'<br>- TIME: 'HH:MM:SS'<br>- TIMESTAMP: 'YYYY-MM-DD HH:MM:SS' optionally with time zone.

Which PostgreSQL type would you use to store just the time of day?

A DATE

B TIME

C TIMESTAMP

D TIMESTAMPTZ

TIME stores only the time of day without any date.

What does TIMESTAMPTZ store that TIMESTAMP does not?

A Time zone information

B Time only

C Milliseconds precision

D Date only

TIMESTAMPTZ stores timestamp with time zone information; TIMESTAMP does not.

How does PostgreSQL display TIMESTAMPTZ values to users?

A Always in UTC

B Without any time zone adjustment

C In the server's local time zone

D In the client's time zone

TIMESTAMPTZ values are converted to the client's time zone on retrieval.

Which format is the default for inputting a DATE in PostgreSQL?

A YYYY-MM-DD

B MM-DD-YYYY

C DD-MM-YYYY

D YYYY/DD/MM

PostgreSQL uses ISO 8601 format 'YYYY-MM-DD' for DATE.

If you want to store a date and time without time zone, which type do you choose?

A DATE

B TIME

C TIMESTAMP

D TIMESTAMPTZ

TIMESTAMP stores date and time without time zone information.

Explain the differences between DATE, TIME, TIMESTAMP, and TIMESTAMPTZ types in PostgreSQL.

Think about what each type includes and how time zones are handled.

Describe how PostgreSQL handles time zones when using the TIMESTAMPTZ type.

Consider storage and display differences.