Postgres – timestamp and datetime in Postgres SQL

DATETIME and TIMESTAMP are two different data types that you can use to store a value that must contain the date and time part.

Similarities of DATETIME and TIMESTAMP
DATETIME and TIMESTAMP have a few similarities that are listed below:

Both stores the same type of data, having two parts (date & time).
Both have the same format when querying them.
Format for storing in the database is the same (YYYY-MM-DD hh:mm:ss).
Both need additional bytes for fractional seconds precision.
Whenever records are updated, both can change data with the current date and time.

Differences of DATETIME and TIMESTAMP
DATETIME and TIMESTAMP have the following differences:
DATETIME and TIMESTAMP require 5 bytes and 4 bytes, respectively.
TIMESTAMP is affected by time zone, but DATETIME remains constant.
Supported range for DATETIME and TIMESTAMP are ‘1000-01-01 00:00:00’to ‘9999-12-31 23:59:59’ and ‘1970-01-01 00:00:01’UTC to ‘2038-01-19 03:14:07’ UTC, respectively.
DATETIME can not be indexed while TIMESTAMP can be.
Queries with TIMESTAMP will be cached, but it is not the case with DATETIME.

Example to Understand the Use of DATETIME and TIMESTAMP in MySQL
Let’s assume that you are running a coffee shop in your country. Each customer gets an invoice after paying the bill.

This invoice has a date and time in addition to other details. As you are running the shop in your country only where all of your customers will be in the same time zone, you will use DATETIME.

Let’s change this scenario a bit; you have ten coffee shops now in various countries, each country has its time zone. The customers will also get invoices there, but how can you display the date and time according to the customers’ time zone.

Here you will use TIMESTAMP. Why? Because TIMESTAMP is affected by time zone, which means, the value of TIMESTAMP will be converted from the current time zone (server’s time) to UTC (Universal Time Zone) for storage and back to the current time zone (server’s time) on retrieval.

#SQL,structured query language,introduction to sql,#RDBMS,#PGClient,Learn SQL,#postgres,#postgresql,#postgresql tutorial,#postgres tutorial,#what is postgresql,#what is postgres,#history of postgresql #postgressql,#postgresql tutorial for beginners,#postgresql course,postgres,postgresql,postgresql tutorial,postgres tutorial,what is postgresql,what is postgres,history of postgresql,postgressql,postgresql tutorial for beginners,postgresql course