Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

[Simon Slavin]

On 20 Mar 2013, at 12:17pm, Dominique Devienne  wrote:

> CURRENT_TIMESTAMP is of text type. Simon's strftime() is also of text type,
> but easily convertible (explicitly via cast, or implicitly via type
> affinity) to an integer. --DD
> 
> C:\Users\DDevienne>sqlite3
> SQLite version 3.7.15.2 2013-01-09 11:53:05
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite>
> sqlite> select CURRENT_TIMESTAMP;
> 2013-03-20 12:12:19
> sqlite> select typeof(CURRENT_TIMESTAMP);
> text
> sqlite> select strftime('%s','now');
> 1363781564
> sqlite> select typeof(strftime('%s','now'));
> text
> sqlite> select cast(strftime('%s','now') as int);
> 1363781631
> sqlite> select typeof(cast(strftime('%s','now') as int));
> integer

Absolutely correct.  However, if the result of the strftime() is stored in an 
INTEGER column of a table, the affinity of that column makes sure that it is 
converted before storage:

SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE myTable (a INTEGER, b TEXT);
sqlite> INSERT INTO myTable VALUES (strftime('%s','now'),strftime('%s','now'));
sqlite> SELECT typeof(a),a,typeof(b),b FROM myTable;
integer|1363788461|text|1363788461

so there's no need to do a CAST if you're immediately storing the value.  On 
the other hand a fussy programmer might prefer to do it anyway.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

[Dominique Devienne]

On Tue, Mar 19, 2013 at 12:51 PM, Philipp Kursawe wrote:

> INSERT INTO test VALUES(CURRENT_TIMESTAMP)
>
> This goes through without an error and the physical db file then really
> contains the current timestamp as a string.


CURRENT_TIMESTAMP is of text type. Simon's strftime() is also of text type,
but easily convertible (explicitly via cast, or implicitly via type
affinity) to an integer. --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> select CURRENT_TIMESTAMP;
2013-03-20 12:12:19
sqlite> select typeof(CURRENT_TIMESTAMP);
text
sqlite> select strftime('%s','now');
1363781564
sqlite> select typeof(strftime('%s','now'));
text
sqlite> select cast(strftime('%s','now') as int);
1363781631
sqlite> select typeof(cast(strftime('%s','now') as int));
integer
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

[Igor Tandetnik]

On 3/19/2013 7:51 AM, Philipp Kursawe wrote:

This goes through without an error and the physical db file then really
contains the current timestamp as a string. How can that be? Is the column
internally converted on the fly?


http://sqlite.org/datatype3.html

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

[Simon Slavin]

On 19 Mar 2013, at 11:51am, Philipp Kursawe  wrote:

> CREATE TABLE test (dt BIG INT)

Note that SQLite does not have a BIG INT type.  The text you show there will 
result in a column with INTEGER affinity.

> and inserted a value:
> INSERT INTO test VALUES(CURRENT_TIMESTAMP)
> 
> This goes through without an error and the physical db file then really
> contains the current timestamp as a string. How can that be? Is the column
> internally converted on the fly?

SQLite does not have strict typing for values stored in tables.  It uses 
'affinities' instead which are more a preference than a 'strict' thing.  If you 
are specific about saying you want a CURRENT_TIMESTAMP then that's what it will 
store, and that is a string.  You can read about affinities in section 2 of 
this page:



You can generate CURRENT_TIMESTAMP as an integer using the datetime functions:



In your case, guessing that you want an integer which represents the current 
time to at least 1 second of precision, I would suggest you use

INSERT INTO test VALUES(strftime('%s','now'))

I have not tried the above code, so please don't take what I wrote as 
definitely what you want, or even definitely working.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

[Stephan Beal]

On Tue, Mar 19, 2013 at 12:51 PM, Philipp Kursawe wrote:

> I created this table
> CREATE TABLE test (dt BIG INT)
> and inserted a value:
> INSERT INTO test VALUES(CURRENT_TIMESTAMP)
>
> This goes through without an error and the physical db file then really
> contains the current timestamp as a string. How can that be? Is the column
> internally converted on the fly?
>

The types you specify for fields are more or less advisory (but do in fact
have an effect on some operations, e.g. MIN()/MAX() calculations really
want number-typed fields). Example:

sqlite> create table t(a BIG INT);
sqlite> insert into t (a) values(7);
sqlite> insert into t (a) values('hi');
sqlite> insert into t (a) values(9.123);
sqlite> select a, typeof(a) from t;
7|integer
hi|text
9.123|real

There are ways to enforce the data type, but (A) i'm not personally
familiar with them, so won't comment and (B) others are and are certainly
formulating their mails in parallel to me.

See also: http://www.sqlite.org/different.html
and search that page for "manifest typing"

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

[Philipp Kursawe]

I created this table
CREATE TABLE test (dt BIG INT)
and inserted a value:
INSERT INTO test VALUES(CURRENT_TIMESTAMP)

This goes through without an error and the physical db file then really
contains the current timestamp as a string. How can that be? Is the column
internally converted on the fly?

Thanks,
Phil
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users