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
asked Aug 28, 2018 at 17:21
Daniel L. VanDenBosch
2,8257 gold badges46 silver badges68 bronze badges
-
You say you've failed with that code, what is the failure message you're getting?esqew– esqew2018-08-28 17:28:41 +00:00Commented Aug 28, 2018 at 17:28
Add a comment
|
2 Answers
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.
1 Comment
Daniel L. VanDenBosch
This answer worked perfectly for what I was trying to do.
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.
