PostgreSQL -TIMESTAMP Data Type
The timestamp data type is used to store date and time values together, including both the date and the time of day. It’s a combination of the date and time information, represented as a single data type. Here are some key points about the timestamp data type:
- Format: Timestamps are stored in the format ‘YYYY-MM-DD HH:MI:SS’, where ‘YYYY’ represents the year, ‘MM’ represents the month, ‘DD’ represents the day of the month, ‘HH’ represents the hours (00 to 23), ‘MI’ represents the minutes (00 to 59), and ‘SS’ represents the seconds (00 to 59). Fractional seconds can also be included.
- Precision: The
timestampdata type can store timestamp values with fractional seconds up to microsecond precision (6 decimal places). - Time Zone: PostgreSQL supports two types of
timestampdata types:timestamp without time zoneandtimestamp with time zone. The former stores a timestamp without considering time zones, while the latter stores a timestamp along with its associated time zone information. - Timestamp Functions: PostgreSQL provides various functions for manipulating and working with
timestampvalues, such as extracting components (year, month, day, hour, minute, second), calculating differences between timestamps, and formatting timestamps for display.
Here’s an example of using the timestamp data type:
CREATE TABLE sensor_readings( reading_id serial PRIMARY KEY,sensor_value numeric,reading_timestamp timestamp );
INSERT INTO sensor_readings (sensor_value, reading_timestamp)VALUES (25.5, '2023-08-16 12:45:00');
SELECT * FROM sensor_readingsWHERE reading_timestamp >= '2023-08-16 12:00:00';
In this example, a reading_timestamp column of type timestamp is used to store timestamp values associated with sensor readings. The timestamp data type is suitable when you need to store and manipulate both date and time information together.