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.
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.
CREATEORREPLACEFUNCTION get_day(unix_timestamp bigint)-- bigint is unix timestamp RETURNStimestamp AS $$ BEGIN RETURN date_trunc('day',to_timestamp(unix_timestamp)) END; $$ LANGUAGE plpgsql;
CREATEORREPLACEFUNCTION get_month(unix_timestamp bigint)-- bigint is unix timestamp RETURNStimestamp AS $$ BEGIN RETURN date_trunc('month',to_timestamp(unix_timestamp)) END; $$ LANGUAGE plpgsql;
Are you sure you want to delete this article section? You can not undo this change.
Add a Comment
Annotate Article
Share Article
Successfully copied article URL to clipboard!
Something went wrong copying the article URL to the clipboard.
Successfully copied editor state to clipboard!
A previous, saved version of this article is saved. To compare the current version of the article with the previous version - or to replace the current version with the saved version - click the icon above the text editor.
Successfully copied URL to clipboard!
Copied code to clipboard.
Copied TeX code to clipboard.
Uploaded file must be an image of type .jpeg, .jpg, .png, .webp, .gif, .bmp, or .svg.
Image Size must be less than 5MB.
Uploaded file must be an image of type jpeg, jpg, png, webp, avif, tiff, or svg.
Something went wrong uploading the image to the database. Try reloading the page.
The maximum number of images you can upload is 30.
There was an error taking an image using the device's camera. Try uploading an image instead.
Uploaded file must be an audio file.
Audio file must be an audio file of type .m4a, .flac, .mp3, .mp4, .wav, .wma, .aac, .webm, or .mpeg.
Audio file must be less than 300 MB in size.
Something went wrong uploading the audio file. Try reloading the page.
The maximum number of audio files you can upload is 10.
There was an error capturing an audio recording using the device's microphone. Try uploading an audio recording or video instead.
Uploaded file must be an video file.
Video file must be an audio file of type .mp4, .mov, .avi, .wmv, .avchd, .webm, or .flv.
Video file must be less than 300 MB in size.
Something went wrong uploading the video file. Try reloading the page.
The maximum number of video files you can upload is 10.
There was an error taking a video using the device's camera / microphone. Try uploading a video instead.
Comments
You have to be logged in to add a comment
User Comments
There are currently no comments for this article.