PostgreSQL date vs timestamp

Introduction

In this article we will explain the difference between PostgreSQL date vs timestamp and when each data type should be utilized. Let’s jump right in!

PostgreSQL DATE

In order to store date values in database, you have to use data type named as DATE in PostgreSQL. DATE has 4 bytes and it uses those 4 bytes to store value in table. The low value is as low as 4713 BC and the high value is as high as 5874897 AD. The format of date that would be stored in database would be yyyy-mm-dd such as 2019-08-07.

If you create a table that has a DATE column and you want it to have the current date in that specific column then you have to use the keyword DEFAULT and the specify CURRENT_DATE. For example, following example would create a table date_table that has the date_saved column with DATE data type. The date_saved column accepts the current date as its default value:

That’s how you would create a table which would essentially store the date value of DATE data type using the CURRENT_DATE. Other than CURRENT_DATE you can also use the built in method or functions to get the current date such as NOW():

The default format in both cases would be yyyy-mm-dd and if you want you can change that to any format you like.

PostgreSQL timestamp

PostgreSQL provides two timestamp related datatypes ‘timestamp’ and ‘timestamptz’. The basic difference between both of them is that the former gives you time without time zone while latter gives you time with time zone. Whenever you use this data type, PostgreSQL stores the values in table in Coordinated Universal Time (UTC) format. PostgreSQL will never save time directly in exact ‘timetamptz’ mode. Essentially, ‘timestamptz’ doesn’t give you time in UTC by default but rather PostgreSQL converts it for you and then save it in database. Whenever you need to know ‘timestamptz’ from database, PostgreSQL converts the UTC saved time back to the local time zone and displays it to you. If we talk about storage then both of these timestamp related data types would take 8 bytes each when they will be used. Both ‘timestamp’ and ‘timestamptz’ have min value of 4713 BC, a max value of 294276 AD, and a resolution of 1 microsecond/ 14 digits.

Conclusion

DATE and timestamp have one main difference and that is that the DATE gives you only current date while timestamp gives you date in addition to the time ( with the option of time zones as well ). You can use DATE wherever you just need to keep track of the date and exact time is unnecessary while you can use timestamp where knowing the time is crucial.