Re: [sqlite] storing and comparing dates in sqlite

[Nick Shaw]

It's your choice, really.  You could store it as TEXT, in some standardised 
format (e.g. XML dateTime format: -mm-ddThh:mm:ss. plus a timezone 
offset if you wanted) or your own format, and query it back as text, or you 
could store it in UNIX integer time (seconds since unix epoch (01/01/1970)) - 
so in an INTEGER field, etc.  Depends how you're going to use it.  Either way 
I've suggested has pros/cons. 

Nick.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of dotolee
Sent: 23 January 2012 18:01
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] storing and comparing dates in sqlite


can you point me in the right direction? 
aka.  what data type am i using to store the date in my sqlite database? is 
TEXT correct? 
how do I do a select on it? 
thanks.


Stephan Beal-3 wrote:
> 
> On Mon, Jan 23, 2012 at 6:51 PM, dotolee <woo_ju...@yahoo.com> wrote:
> 
>>
>> i'm new to sqlite... and relatively new to php.  just wondering what 
>> the best way is to store and compare dates.
>>
> 
> For any given 10 developers you'll likely hear 11 opinions on this topic.
> IMO Unix timestamps are the most portable form out there. "Portable"
> meaning, in this context, the ability to work with them (more or less
> easily) in a wide variety of contexts.
> 
> 
> --
> - 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
> 
> 

--
View this message in context: 
http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33189862.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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

Re: [sqlite] storing and comparing dates in sqlite

[dotolee]

i think i got it. 

echo date("M-d-Y H:i:s", mktime());
date() is php. 

thanks. 


dotolee wrote:
> 
> i'm storing as integers now in the database. 
> getting the unix time in seconds using the mktime() method. 
> now i just need to figure out how to display properly. 
> 
> for example, in the sample below, i'm having a hard time getting the
> minutes to show up as minutes.  right now, the "m" is displaying the
> month. 
> 
> echo date("M-d-Y H:m:s", mktime());
> 
> i've read http://www.sqlite.org/lang_datefunc.html but i can't seem to get
> it to work.
> 
> 
> Simon Slavin-3 wrote:
>> 
>> 
>> On 23 Jan 2012, at 5:53pm, Stephan Beal wrote:
>> 
>>> On Mon, Jan 23, 2012 at 6:51 PM, dotolee <woo_ju...@yahoo.com> wrote:
>>> 
>>>> i'm new to sqlite... and relatively new to php.  just wondering what
>>>> the
>>>> best way is to store and compare dates.
>>> 
>>> For any given 10 developers you'll likely hear 11 opinions on this
>>> topic.
>> 
>> How true.  For what it's worth, here's mine:
>> 
>> 1) read
>> 
>> http://www.sqlite.org/lang_datefunc.html
>> 
>> 2) If you need to do maths on dates (how many days apart are two dates,
>> what's three days after this date, etc.) store your dates as numbers,
>> either julianday or unixepoch.
>> 
>> 3) Otherwise store them as text, in MMDD format, so they're easy to
>> read when you're debugging.
>> 
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> 

-- 
View this message in context: 
http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33190888.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] storing and comparing dates in sqlite

[dotolee]

i'm storing as integers now in the database. 
getting the unix time in seconds using the mktime() method. 
now i just need to figure out how to display properly. 

for example, in the sample below, i'm having a hard time getting the minutes
to show up as minutes.  right now, the "m" is displaying the month. 

echo date("M-d-Y H:m:s", mktime());

i've read http://www.sqlite.org/lang_datefunc.html but i can't seem to get
it to work.


Simon Slavin-3 wrote:
> 
> 
> On 23 Jan 2012, at 5:53pm, Stephan Beal wrote:
> 
>> On Mon, Jan 23, 2012 at 6:51 PM, dotolee <woo_ju...@yahoo.com> wrote:
>> 
>>> i'm new to sqlite... and relatively new to php.  just wondering what the
>>> best way is to store and compare dates.
>> 
>> For any given 10 developers you'll likely hear 11 opinions on this topic.
> 
> How true.  For what it's worth, here's mine:
> 
> 1) read
> 
> http://www.sqlite.org/lang_datefunc.html
> 
> 2) If you need to do maths on dates (how many days apart are two dates,
> what's three days after this date, etc.) store your dates as numbers,
> either julianday or unixepoch.
> 
> 3) Otherwise store them as text, in MMDD format, so they're easy to
> read when you're debugging.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33190861.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] storing and comparing dates in sqlite

[Igor Tandetnik]

On 1/23/2012 12:51 PM, dotolee wrote:

i'm new to sqlite... and relatively new to php.  just wondering what the best
way is to store and compare dates.
right now i have something like this in my php code:
$curr_date_time = strftime('%Y-%m-%d %H:%M:%S');
i'm inserting this into a field in the database that has been defined as
type TEXT because from what i read, sqlite does not have a date datatype.
but question is how can i write a select statement that will sort by date?


select * from MyTable order by MyDateColumn;

With this format, alphabetical order happens to match calendar order. No 
additional magic required.



the sql i have right now is not working because it's not treating the field
as a true date...


Show the SQL you have right now, sample data, and the query results. 
Explain how actual results differ from your expectations.



i want to do something like:

select field1, field2, updated from myhistory group by field1 order by
updated desc


This query doesn't make much sense. Suppose you have ten rows with 
field1='X', but with different values of field2 and updated. GROUP BY 
clause will produce one row representing this group, where field1='X', 
while field2 and updated would be taken from some random row in the 
group (not even necessarily the same one). Is this what you had in mind?

--
Igor Tandetnik

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

Re: [sqlite] storing and comparing dates in sqlite

[Simon Slavin]

On 23 Jan 2012, at 5:53pm, Stephan Beal wrote:

> On Mon, Jan 23, 2012 at 6:51 PM, dotolee  wrote:
> 
>> i'm new to sqlite... and relatively new to php.  just wondering what the
>> best way is to store and compare dates.
> 
> For any given 10 developers you'll likely hear 11 opinions on this topic.

How true.  For what it's worth, here's mine:

1) read

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

2) If you need to do maths on dates (how many days apart are two dates, what's 
three days after this date, etc.) store your dates as numbers, either julianday 
or unixepoch.

3) Otherwise store them as text, in MMDD format, so they're easy to read 
when you're debugging.

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

Re: [sqlite] storing and comparing dates in sqlite

[Stephan Beal]

On Mon, Jan 23, 2012 at 7:01 PM, dotolee  wrote:

> can you point me in the right direction?
> aka.  what data type am i using to store the date in my sqlite database? is
> TEXT correct?
> how do I do a select on it?
>

http://en.wikipedia.org/wiki/Unix_time

-- 
- 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

Re: [sqlite] storing and comparing dates in sqlite

[dotolee]

can you point me in the right direction? 
aka.  what data type am i using to store the date in my sqlite database? is
TEXT correct? 
how do I do a select on it? 
thanks.


Stephan Beal-3 wrote:
> 
> On Mon, Jan 23, 2012 at 6:51 PM, dotolee <woo_ju...@yahoo.com> wrote:
> 
>>
>> i'm new to sqlite... and relatively new to php.  just wondering what the
>> best
>> way is to store and compare dates.
>>
> 
> For any given 10 developers you'll likely hear 11 opinions on this topic.
> IMO Unix timestamps are the most portable form out there. "Portable"
> meaning, in this context, the ability to work with them (more or less
> easily) in a wide variety of contexts.
> 
> 
> -- 
> - 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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33189862.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] storing and comparing dates in sqlite

[Stephan Beal]

On Mon, Jan 23, 2012 at 6:51 PM, dotolee  wrote:

>
> i'm new to sqlite... and relatively new to php.  just wondering what the
> best
> way is to store and compare dates.
>

For any given 10 developers you'll likely hear 11 opinions on this topic.
IMO Unix timestamps are the most portable form out there. "Portable"
meaning, in this context, the ability to work with them (more or less
easily) in a wide variety of contexts.


-- 
- 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] storing and comparing dates in sqlite

[dotolee]

i'm new to sqlite... and relatively new to php.  just wondering what the best
way is to store and compare dates. 
right now i have something like this in my php code:
$curr_date_time = strftime('%Y-%m-%d %H:%M:%S');
i'm inserting this into a field in the database that has been defined as
type TEXT because from what i read, sqlite does not have a date datatype. 
but question is how can i write a select statement that will sort by date? 
the sql i have right now is not working because it's not treating the field
as a true date...
i want to do something like: 

select field1, field2, updated from myhistory group by field1 order by
updated desc

-- 
View this message in context: 
http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33189769.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users