LightDB数据库支持datetime类型

在MySQL中datetime存储包含日期和时间的值。 当从datetime列查询数据时，MySQL会以以下格式显示datetime值：

YYYY-MM-DD HH:MM:SS。默认情况下，datetime的值范围为1000-01-01 00:00:00至9999-12-31 23:59:59。

当前在LightDB数据库（包括LightDB-X和LightDB-A）已经支持了datetime类型，其实际功能等效于timestamp without time zone。详细使用举例如下：

-- Test alias for datetime
-- test timestamp
-- test timestamp cast
select '2014-12-15 00:20:59'::datetime;
timestamp
--------------------------
Mon Dec 15 00:20:59 2014
(1 row)

select datetime '2014-12-15 00:20:59';
timestamp
--------------------------
Mon Dec 15 00:20:59 2014
(1 row)

-- test create table
--
Create table datetime
(
datetime datetime,
datetime1 date,
primary key (datetime)
);
\d+ datetime;
Table "public.datetime"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
datetime | timestamp without time zone | | not null | | plain | |
datetime1 | date | | | | plain | |
Indexes:
"datetime_pkey" PRIMARY KEY, btree ("datetime")

insert into datetime values('2014-12-15 00:20:59','2014-12-15');
insert into datetime values('2014-12-15 00:20:00'::datetime,date '2014-12-15');
insert into datetime values(datetime '2014-12-16 00:20:00',date '2014-12-15');
select * from datetime;
datetime | datetime1
--------------------------+------------
Mon Dec 15 00:20:59 2014 | 12-15-2014
Mon Dec 15 00:20:00 2014 | 12-15-2014
Tue Dec 16 00:20:00 2014 | 12-15-2014
(3 rows)

alter table datetime rename COLUMN datetime to datetime0;
alter table datetime rename COLUMN datetime1 to datetime;
alter table datetime alter column datetime type datetime;
\d+ datetime;
Table "public.datetime"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
datetime0 | timestamp without time zone | | not null | | plain | |
datetime | timestamp without time zone | | | | plain | |
Indexes:
"datetime_pkey" PRIMARY KEY, btree (datetime0)

 

drop table datetime;
-- test datetime useing for function
create function "datetime"(datetime) returns datetime as
$$
select '2014-07-29 12:59:59'::datetime;
$$ language sql;
drop function "datetime";
create function datetime2(datetime) returns datetime as
$$
select '2014-07-29 12:59:59'::datetime;
$$ language sql;
drop function datetime2;