How do I insert a 'Time' value into a table

[Andrew Lindsay]

Hopefully the last question for tonight.

How do I insert the following value into a table with a field that is labeled as TIME.

The Value is "30/04/2009 0:00".

I do not get any errors when I insert the record, but when I look at the value in SQLite Maestro, it seems to have stored the value as "20:09:00".  Any asistance would be appreciated.

Regards

Andrew

[Rolf Brandt]

Hello Andrew,

to see how DateTime data is stored look at this document:

http://www.sqlite.org/lang_datefunc.html

The best way to store date and time in a sqlite database probably would be YYYY-MM-DD HH:MM:SS or YYYY-MM-DD HH:MM (if you don't need the seconds). That makes it easier to sort properly after this field.

The Value is "30/04/2009 0:00" would best be stored as

"2009-04-30 00:00"

SQLite Maestro probably expects this format and interpretes your value like that. SQLite Expert does the same. There is no real storage class or column affinity for date or time. It is in reality
a text field. See

http://www.sqlite.org/datatype3.html

for more info.

Rolf

[Bern Ertl]

See also:

http://planetsquires.com/support/index.php?topic=2939.0

[Paul Squires]

http://www.mail-archive.com/sqlite-users@sqlite.org/msg40689.html

[Fred Meier]

I agree with Rolf's post:

SQLite Maestro probably expects this format and interpretes your value like that. SQLite Expert does the same. There is no real storage class or column affinity for date or time. It is in reality
a text field. See

I also use SQLite Expert, and like it very much, but you must be VERY aware that theses great tools sometimes distort how SQLite actually works.

[cj]

-- Date/times are UTC (if running remote server time is used) ;
create table if not exists t(mydate);
insert into t values(datetime('now'));       -- insert using UTC;
select datetime(mydate,'localtime') from t;  -- use local time;
select round((julianday('now','localtime') - julianday('now'))*24);
select time('now','localtime');
select date('now','localtime');

http://www.sqlite.org/lang_datefunc.html