CODEWITHSUNDEEP
postgresql
Daniel L. VanDenBosch
Daniel L. VanDenBosch

Reputation: 2724

POSTGRES datetime2 to timestamp

I am migrating to postgres from sql server. Is there a way that I could do the following and have it still work? SELECT CAST('1/1/2018' AS datetime2) I have tried and failed with 

   CREATE TYPE datetime2 AS (date2 TIMESTAMP); --doesn't work

Upvotes: 4

Views: 8083

Answers (2)

Abelisto
Abelisto

Reputation: 15624

If you want to have datetime2 as a synonym for timestamp type in PostgreSQL then use domain instead of type:

create domain datetime2 as timestamp;

Then your cast will work as is:

SELECT CAST('1/1/2018' AS datetime2);
┌─────────────────────┐
│      datetime2      │
├─────────────────────┤
│ 2018-01-01 00:00:00 │
└─────────────────────┘

Except the name it will have same behavior as original timestamp type.

What you are doing in your question is to create composite type (record with single field date2 of timestamp type) which could be also highly useful but for another kinds of tasks.

Upvotes: 4

Dr G.
Dr G.

Reputation: 1332

I think what you want is:

SELECT '1/1/2018' AS TIMESTAMP;

  timestamp 
 -----------
  1/1/2018

Or

SELECT CAST('1/1/2018' AS TIMESTAMP); 

      timestamp      
 ---------------------
  2018-01-01 00:00:00

Or

SELECT CAST('1/1/2018' AS DATE);

    date    
------------
 2018-01-01

It depends really on what you want to do.

Upvotes: 0

Related Questions