Can't change the type of a column from date to integer

Question

I'm currently trying to use the solution found here, but I don't seem to be able to change the type of the users column from date to integer.

When I try:

ALTER TABLE users ALTER COLUMN password_reset_expires TYPE integer;

I get this error:

ERROR:  column "password_reset_expires" cannot be cast automatically to type integer
HINT:  You might need to specify "USING password_reset_expires::integer".

And when I try:

ALTER TABLE users ALTER COLUMN password_reset_expires TYPE integer
USING (password_reset_expires::integer);

The error tells me:

ERROR:  cannot cast type date to integer
LINE 1: ...expires TYPE integer USING (password_reset_expires::integer)...

My table definition:

                                            Table "public.users"
         Column         |          Type          | Collation | Nullable |              Default
------------------------+------------------------+-----------+----------+-----------------------------------
 id                     | bigint                 |           | not null | nextval('users_id_seq'::regclass)
 name                   | character varying(20)  |           | not null |
 email                  | character varying(100) |           | not null |
 password               | character varying(500) |           | not null |
 password_changed_at    | date                   |           |          |
 password_reset_token   | character varying(300) |           |          |
 password_reset_expires | date                   |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "tokens" CONSTRAINT "tokens_token_pk_fkey" FOREIGN KEY (fk_users_id) REFERENCES users(id)

Here is one of my user objects:

{
  id: '59',
  name: 'visitor',
  email: 'visitor',
  password: '$2b$10$0UGgdRUlXFYeQfk5Nv/vXe9khdzyyOqsTiFGyNXLfEDuOFAt0xc1G',
  password_changed_at: null,
  password_reset_token: null,
  password_reset_expires: null
}

Please provide sample data and desired results. It is not obvious what value you want for a given date. — Gordon Linoff, Jul 09 '20 at 21:54
@GordonLinoff just added an example of an user at the end. What I want to store in `password_reset_expires` and `password_changed_at` is a number like this 1594329364292 — Ramiro Herrera, Jul 09 '20 at 21:59
Why would you want to do that to begin with? Date values should be stored in `date` columns and date/time values should be stored in `timestamp` or `timestamptz` columns. Don't use the stupid epoch to do that. https://blog.sql-workbench.eu/post/epoch-mania/ — , Jul 10 '20 at 05:17

Erwin Brandstetter · Answer 1 · 2020-07-09T23:23:38.617

Basically, use the SQL-standard function EXTRACT(). Your comment:

What I want to store in password_reset_expires and password_changed_at is a number like this 1594329364292

... indicates you want to store the number of milliseconds since the UNIX epoch 1970-01-01 00:00:00 as integer value. But that's out of range for type integer (signed int4), which allows numbers up to 2^31 - 1 = 2147483647. At the time of writing, already 1594335691272 milliseconds have passed since the epoch.

Either store seconds in an integer column:

ALTER TABLE users ALTER COLUMN password_reset_expires TYPE integer
USING (EXTRACT(EPOCH FROM password_reset_expires)::int);

Or store milliseconds in a bigint column
(signed int8, allows up to 2^63 - 1 = 9223372036854775807):

ALTER TABLE users ALTER COLUMN password_reset_expires TYPE bigint -- !
USING (EXTRACT(EPOCH FROM password_reset_expires)::bigint * 1000);

EXTRACT(EPOCH FROM ...) returns, according to the manual:

For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

Since the source is date in this case, there cannot be fractional seconds and we can just multiply by 1000 to get the number of milliseconds.

Then again, if there cannot be milliseconds, why store the number of milliseconds to begin with?

More importantly, why not keep date? If the value actually represents a date, the data type date is almost certainly the best option.

Can't change the type of a column from date to integer

1 Answers1