CODEWITHSUNDEEP
postgresqldatetime
jirikadlec2
jirikadlec2

Reputation: 1276

smalldatetime in PostgreSQL

I have a PostgreSQL database with millions of date/time data rows. The time range of the data is from year 2004 until now. The time zone is always UTC. Due to limited disk space on my webhosting account, I want to reduce the database size as much as possible. I know that in Microsoft SQL Server there is a SMALLDATETIME datatype with 4 byte size.

Is there something equivalent to SMALLDATETIME in Postgres?

(Note: I have looked at the Postgres manual, but all 'date and time' are 8 bytes. the 4-byte 'date' datatype doesn't suit my needs because I need resolution in minutes.)

Upvotes: 3

Views: 6110

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324751

No, there isn't such a type. It'd be nice for a few use cases, but it isn't supported.

What you can do as a compromise is store the seconds since your offset epoch:

extract(epoch from mydate) - extract(epoch from TIMESTAMP '2004-01-01 00:00:00')

in an integer column, then reconstruct the date on the fly. This will have a performance impact, and more importantly, suck for readability, but it'll work.

SELECT to_timestamp(mydatesecondssince2004 + extract(epoch from TIMESTAMP '2004-01-01 00:00:00')) FROM mytable

Alternately, you could be the person who decides to add the 'smalldate' and 'smalltimestamp' types; PostgreSQL is open source, and that means people need to do things if they want them to happen.

Upvotes: 4

Related Questions