• Welcome, Guest. Please login.
 
August 19, 2019, 08:15:20 am

News:

Welcome to the SQLitening support forums!


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

Started by Andrew Lindsay, June 29, 2009, 12:32:00 pm

Previous topic - Next topic

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

June 29, 2009, 12:50:59 pm #1 Last Edit: June 29, 2009, 12:53:56 pm by 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
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu



Fred Meier

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.

cj

August 19, 2014, 10:15:12 am #5 Last Edit: August 20, 2014, 03:15:34 am by 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