can't create a time/date field in graphical mode.

[Former user]

Originally reported on Google Code with ID 76

What steps will reproduce the problem?
1. it's only available to select integer,blob,text,real types when inserting,editing
a column.
2. but it seems steel to be able to create a table with a help of an sql statement,
and set def. value of column to CURRENT_TIME. inserting lines in a table seems to be
working ok after this. curr. date fills the field if wasn't specified manually.
3.

What is the expected output? What do you see instead?

But when i try to use NOW statement in a DATETIME field it gives me an error:
Warning: SQLite3::query() [sqlite3.query]: Unable to prepare statement: 1, no such
column: NOW in <filepath> on line 669

What version of the product are you using? On what operating system?
gentoo, define("VERSION", "1.9.0");

Please provide any additional information below.

some other types aren't available to set in a graph mode. such as varchar.

Reported by remort.net on 2011-12-13 19:40:55

[Dane Iracleous]

``` I can't understand what you wrote. I'm sorry. ```

Reported by `diracleo` on 2011-12-15 05:44:07

[Former user]

``` I can't create datetime field and set default value to NOW. It works in firefox sqlite manager plugin and from native sqlite linux console utility. But i can't do it from web-interface of phpliteadmin.

I can create table through SQL query like 'create table..."reg_date" DATETIME NOT NULL DEFAULT now,...'. It should work, but what about grafical mode? ```

Reported by `remort.net` on 2011-12-15 06:02:49

[Dane Iracleous]

``` I'm not an expert on SQLite, but I am pretty sure there is no explicit DATETIME data type. Representing dates in SQLite requires that you set the column to either INTEGER, REAL, or TEXT, and then using a function: http://www.sqlite.org/lang_datefunc.html

Here's the docs on data types: http://www.sqlite.org/datatype3.html ```

Reported by `diracleo` on 2011-12-15 07:03:17

[Former user]

``` Ok, let's create new table in web-inteface of phpliteadmin. with 1 column only.

When we start to configure columns properties , we can choose only (integer|real|text|blob). thereis no Date type which is a hack in a bunch of other utilities, so we use only four native types in phpliteadmin, mentioned before.

I tried to make an Integer or Text field with not-null flag and "date(now)" in default-value field. But it does't work. "date(now)" works like a text string, not like a function. So i cant find a way to create date field with default value...

Update:

I googled out a way. 1. Tne console way:

create table tbl1(id int primary key, dt datetime default current_timestamp); insert into tbl1(id) VALUES (1); select * from tbl1; 1|2011-12-15 07:26:59

So it shows that there are multiple abstract datatypes possible to use in sqlite. But only 4 are matematically different. 4 base types and a bunch of higher level abstract ones. It shown in a table here: http://www.sqlite.org/datatype3.html (Look into table).

2. After the console method i tried phpliteadmin method. The code was: CREATE TABLE date_table(id INTEGER, datefield INTEGER NOT NULL default current_timestamp ) when i insert a row into date_table table, it fills 2011-12-15 07:28:25, so it works.

It would be very great if you could add all the abstract types to phpliteadmin. Other sqlite utilities uses these types. But anyway, now i know how to make what i want in phpliteadmin.

15.12.2011 13:03, [e-mail] ?????: ```

Reported by `remort.net` on 2011-12-15 08:56:05

[Dane Iracleous]

``` This is a good suggestion for a new feature. ```

Reported by `diracleo` on 2011-12-16 05:24:35 - Status changed: `Started`

[Former user]

``` i need to create a table with timefields, default value of sould be like this: (strftime('%d-%m-%Y')) . i can create a simple table through an SQL query form:

CREATE TABLE "test" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , "add_date" INTEGER NOT NULL DEFAULT (strftime('%d-%m-%Y')))

it creates right. but when i try to insets a row in it - add_date column def. value is strftime( in form field. quotes problem again. how can i add a empty row with default value of time field like (strftime('%d-%m-%Y')) ? ```

Reported by `remort.net` on 2011-12-26 10:52:25

[Christopher Kramer]

comment number 6 is another example for issue #55.
Being able to add date/time fields is another issue, though.

Reported by crazy4chrissi on 2013-03-19 23:36:08 - Status changed: Accepted - Labels added: Type-Enhancement, Target-1.9.5 - Labels removed: Type-Defect

[Christopher Kramer]

This issue was closed by revision r435.

Reported by crazy4chrissi on 2013-12-25 20:28:46 - Status changed: Fixed

[Christopher Kramer]

Reported by crazy4chrissi on 2013-12-25 20:29:04