PostgreSQL Date/Time Functions and Operators

I store dates as a bigint UNIX time in my databases, which I think is simple and I like, but when looking at data, this big integer gives me no meaning so I need to use postgresql functions to change the format. I always have to look these functions up, and I am looking to learn/remember some of these functions so that I don't always have to look them up.

Date Created:

2 526

References

Notes

PostgreSQL supports the full set of SQL date and time types.


Name	Storage Size	Description	Low Value	High Value	Resolution
`timestamp [ (``p``) ] [ without time zone ]`	8 bytes	both date and time (no time zone)	4713 BC	294276 AD	1 microsecond
`timestamp [ (``p``) ] with time zone`	8 bytes	both date and time, with time zone	4713 BC	294276 AD	1 microsecond
`date`	4 bytes	date (no time of day)	4713 BC	5874897 AD	1 day
`time [ (``p``) ] [ without time zone ]`	8 bytes	time of day (no date)	00:00:00	24:00:00	1 microsecond
`time [ (``p``) ] with time zone`	12 bytes	time of day (no date), with time zone	00:00:00+1559	24:00:00-1559	1 microsecond
`interval [` `fields` `] [ (``p``) ]`	16 bytes	time interval	-178000000 years	178000000 years	1 microsecond

The time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. The allowed range of p is 0 to 6. Date and time input is accepted in any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others.

The to_timestamp function can be used to convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone.

to_timestamp(double precision) -> timestamp with timezone
to_timestamp(1284352323) → 2010-09-13 04:32:03+00

The date_trunc function can be used to truncate a timestamp to a specified precision.

date_trunc ( text, timestamp ) → timestamp
date_trunc('hour', timestamp '2001-02-16 20:38:40') → 2001-02-16 20:00:00

Valid values for the first argument in date_trunc are:

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millenium

The date_part function can be used to get a timestamp subfield.

date_part ( text, timestamp ) → double precision
date_part('hour', timestamp '2001-02-16 20:38:40') → 20

Maybe Some Useful Functions

CREATE OR REPLACE FUNCTION get_day(unix_timestamp bigint) -- bigint is unix timestamp 
RETURNS timestamp
AS 
$$ 
	BEGIN
		RETURN date_trunc('day',to_timestamp(unix_timestamp))
	END;	
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_month(unix_timestamp bigint) -- bigint is unix timestamp 
RETURNS timestamp
AS 
$$ 
	BEGIN
		RETURN date_trunc('month',to_timestamp(unix_timestamp))
	END;	
$$ LANGUAGE plpgsql;

PostgreSQL Date/Time Functions and Operators

References

Notes

Maybe Some Useful Functions

Comments

User Comments