Postgresql Alter Column Data Type to Timestamp Without Time Zone

postgreSQL alter column data type to timestamp without time zone

If create_time is of type TEXT with valid date value, it'll be easier to proceed with the change as follows (Be advised to first do a table dump as a backup):

-- Create a temporary TIMESTAMP column
ALTER TABLE AB ADD COLUMN create_time_holder TIMESTAMP without time zone NULL;

-- Copy casted value over to the temporary column
UPDATE AB SET create_time_holder = create_time::TIMESTAMP;

-- Modify original column using the temporary column
ALTER TABLE AB ALTER COLUMN create_time TYPE TIMESTAMP without time zone USING create_time_holder;

-- Drop the temporary column (after examining altered column values)
ALTER TABLE AB DROP COLUMN create_time_holder;

PostgreSQL alter type timestamp without time zone - with time zone

It keeps the current value in localtime and sets the timezone to your localtime's offset:

create table a(t timestamp without time zone, t2 timestamp with time zone);
insert into a(t) values ('2012-03-01'::timestamp);
update a set t2 = t;
select * from a;
          t          |           t2           
---------------------+------------------------
 2012-03-01 00:00:00 | 2012-03-01 00:00:00-08

alter table a alter column t type timestamp with time zone;
select * from a;
           t            |           t2           
------------------------+------------------------
 2012-03-01 00:00:00-08 | 2012-03-01 00:00:00-08

According to the manual for Alter Table:

if [the USING clause is] omitted, the default conversion is the same as an assignment cast from old data type to new.

According to the manual for Date/Time types

Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.

Postgres alter column from time without time zone to int

This should do it:

 ALTER TABLE your_table 
     ALTER COLUMN time TYPE integer USING 0;

ALTER COLUMN is usually only used when you want to preserve the data in that column. In your case the above should work, but doesn't buy you anything. Dropping the column and then adding a new one with the type integer and a default value of 0 would be just as efficient.

(Btw: you should not use a reserved word like time as a column name)

Edit

If you do want to convert the existing data then you can use this:

 ALTER TABLE your_table 
     ALTER COLUMN time_column TYPE integer USING extract(epoch from time_column);

Change column type from timestamp WITHOUT time zone to timestamp WITH time zone

The example in the Postgres manual (as well as the working fiddle by @mvp) transform an integer column (representing a UNIX epoch) to timestamptz.

The error message as well as your title clearly indicate you are trying to convert a timestamp to timestamptz. And this just works automatically, without explicit cast.

ALTER TABLE test ALTER created_at TYPE timestamptz;

-> SQLfiddle.

More about timestamp vs. timestamptz:

Ignoring timezones altogether in Rails and PostgreSQL

timestamp values are always interpreted according to the time zone setting of your session. To assume the time zone UTC for the conversion:

BEGIN;
SET LOCAL timezone='UTC';
ALTER TABLE test ALTER created_at TYPE timestamptz;
COMMIT;

Or use the AT TIME ZONE construct:

ALTER TABLE test ALTER created_at TYPE timestamptz
USING created_at AT TIME ZONE 'UTC';

You can assume any time zone this way.

Change column type from character varying to timestamp without time zone

.. are there any functions I can use as strToTimeStamp that can convert
character varying type to timestamp without time zone type?

Use to_timestamp() to convert string data to type timestamp and alter the data type of the column in place:

ALTER TABLE tbl ALTER COLUMN col TYPE timestamp
USING to_timestamp(col, '<your pattern here>');

See:

• Alter character field to date
• Cast varchar type to date

column birthdate is of type timestamp without time zone but expression is of type text

just change the order of the columns in the INSERT so that it corresponds to the order of the values to be inserted :

INSERT INTO profile.profile(userId,name,gender,birthDate)

Alter column type from int or bigint to timestamp

ALTER ... USING statement.

Test data, from your sample.

CREATE TABLE groupgps AS
SELECT date_stamp::bigint
FROM generate_series(1,100) AS date_stamp;

With to_timestamp()

It seems you can also use the to_timestamp function. The docs pointing to to_timestamp claim that this,

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';

The to_timestamp function encapsulates the above conversion.

So we can use that in our ALTER TABLE ... USING too,

ALTER TABLE groupgps
    ALTER COLUMN date_stamp SET DATA TYPE timestamp with time zone
    USING to_timestamp(date_stamp);

Without using to_timestamp()

Then we adapt the example from the docs.

ALTER TABLE groupgps
    ALTER COLUMN date_stamp SET DATA TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + date_stamp * interval '1 second';

How to convert time without time zone to timestamp without time zone?

If you want the date part to be today:

alter table the_table
alter column the_column type timestamp without time zone 
using current_date + the_column

---

Related Topics