SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Andrew Lindsay on June 29, 2009, 12:32:00 pm

Title: How do I insert a 'Time' value into a table
Post by: Andrew Lindsay on June 29, 2009, 12:32:00 pm
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
Title: Re: How do I insert a 'Time' value into a table
Post by: Rolf Brandt on June 29, 2009, 12:50:59 pm
Hello Andrew,

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

http://www.sqlite.org/lang_datefunc.html (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 (http://www.sqlite.org/datatype3.html)

for more info.

Rolf
Title: Re: How do I insert a 'Time' value into a table
Post by: Bern Ertl on June 29, 2009, 01:01:29 pm
See also:

http://planetsquires.com/support/index.php?topic=2939.0
Title: Re: How do I insert a 'Time' value into a table
Post by: Paul Squires on June 29, 2009, 02:39:15 pm
http://www.mail-archive.com/sqlite-users@sqlite.org/msg40689.html
Title: Re: How do I insert a 'Time' value into a table
Post by: Fred Meier on June 29, 2009, 04:50:10 pm
I agree with Rolf's post:
QuoteSQLite 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.
Title: Re: How do I insert a 'Time' value into a table
Post by: cj on August 19, 2014, 10:15:12 am
-- 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