If speed and storage are a concern then as somebody else noted....julianday is
the way to go.
Just don't confuse CURRENT_TIMESTAMP with CURRENT_TIME -- you can still extract
just date from the field if you need it.
sqlite> create table t(id,time);
sqlite> insert into t values(1,julianday(CURRENT_TIMESTAMP));
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(id,time);
INSERT INTO "t" VALUES(1,2456236.05462963); -- you can see storage mode is
double
COMMIT;
sqlite> select id,date(time) from t;
1|2012-11-04
sqlite> select id,datetime(time) from t;
1|2012-11-04 13:18:40
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Sunday, November 04, 2012 1:34 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"
Fri, 2 Nov 2012 14:11:26 +0000 от "Black, Michael (IS)"
<michael.bla...@ngc.com>:
>CREATE TABLE t(id,time);
>
INSERT INTO t VALUES(1,CURRENT_DATE);
>
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
>
INSERT INTO t VALUES(3,datetime('now'));
>
INSERT INTO t VALUES(4,date('now'));
>
SELECT * FROM t;
>
1|2012-11-02
>
2|2012-11-02 14:10:15
>
3|2012-11-02 14:10:15
>
4|2012-11-02
>
>
Perhaps the documentation needs to be better? Apparently you couldn't find
this info...
Indeed, I was never aware of CURRENT_*.
Anyway, all these functions return current moment as _string_ and this is not a
great way to store datetime in db, isn't it?
- more memory occupied;
- slower compare;
- cannot add & substract;
etc.
>
>
Michael D. Black
>
Senior Scientist
>
Advanced Analytics Directorate
>
Advanced GEOINT Solutions Operating Unit
>
Northrop Grumman Information Systems
>
>
________________________________________
>
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Григорий Григоренко [grigore...@mail.ru]
>
Sent: Friday, November 02, 2012 8:08 AM
>
To: General Discussion of SQLite Database
>
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"
>
>
Thu, 1 Nov 2012 19:57:42 +0000 от Simon Slavin <slav...@bigfraud.org>:
>
>
>
>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote:
>
>
>
>
>
> it is a common practice to store datetime values as UNIX time UTC.
>
>
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
>
>
>
Please read
>
>
>
>
>
<http://www.sqlite.org/lang_datefunc.html>
>
>
>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
functions that modify or format date values.
>
>
It's about having useful shortcut for getting current moment that doesn't have
(string) parameters and so can be easily remembered and typed.
>
>
Compare:
>
MS SQL: CURRENT_TIMESTAMP
>
PostgreSQL: now()
>
Oracle: sysdate
>
>
To:
>
Sqlite: strftime('%s','now')
>
>
>
>Simon.
>
_______________________________________________
>
sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
