Using time and date values

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Using time and date values

Henning Folger
Hi,

I am looking for a simple way of using date and time values with
sqlite3. I have some Ctime classes (VC++.net) which have to be stored in
the database and they should be compared.

Anyone has an idea?

Henning

Reply | Threaded
Open this post in threaded view
|

Re: Using time and date values

Teg-3
Hello Henning,

CTime will give you access to the "time_t" field. I just store the
time_t in the DB. It's an unsigned long that represents seconds since
Jan 1, 1970. You can feed a time_t back into CTime to initialize it.

Be aware though that Microsoft is changing what "time(NULL)" returns.
I read a warning in the SDK that says, they're changing it to seconds
since some time in the 1500's (no joke). It apparently only affects
VC7 and above.

I'm speaking of the MFC CTime class. Hopefully .net implements the
same interface.

C.


Wednesday, December 14, 2005, 11:15:27 AM, you wrote:

HF> Hi,

HF> I am looking for a simple way of using date and time values with
HF> sqlite3. I have some Ctime classes (VC++.net) which have to be stored in
HF> the database and they should be compared.

HF> Anyone has an idea?

HF> Henning




--
Best regards,
 Teg                            mailto:[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Using time and date values

Brad-19
In reply to this post by Henning Folger
> I am looking for a simple way of using date and time values with
> sqlite3. I have some Ctime classes (VC++.net) which have to be stored
> in
> the database and they should be compared.

If the class you're using has .Value property, or a way to easily
transform the date from one format to another, you should probably store
that.  For instance, I like to store dates in string format, using
something like "CCYYMMDDHHmmSS.SSSSSS". If I expect to need to deal with
time zones, I'll store all the dates/times as GMT, or Universal time,
and do the conversions when I create the datetime objects.


Reply | Threaded
Open this post in threaded view
|

Re: Using time and date values

John Stanton-3
Brad wrote:

>> I am looking for a simple way of using date and time values with
>> sqlite3. I have some Ctime classes (VC++.net) which have to be stored in
>> the database and they should be compared.
>
>
> If the class you're using has .Value property, or a way to easily
> transform the date from one format to another, you should probably store
> that.  For instance, I like to store dates in string format, using
> something like "CCYYMMDDHHmmSS.SSSSSS". If I expect to need to deal with
> time zones, I'll store all the dates/times as GMT, or Universal time,
> and do the conversions when I create the datetime objects.
>
>
You might consider storing dates and times in ISO9601 format, and doing
as suggested above, using Zulu time (also known as UTC, Universal
Co-ordinated Time, formerly GMT).  By complying with a standard and
using UTC you can present the time according to time zone and daylight
saving status.  In a widely distributed system the complication is
keeping track of daylight saving, but there are databases and tools
which help.

The other way to store time is an offset from an epoch.  The problem
there is that there are arbitrary epochs.  The best one is some date BC
which has the magic property that it can be used to transform the offset
into all of the major world date systems, like Gregorian, Arabic,
Hebrew, Japanese etc.

If you application is fairly straightforward the ISO9601 format has the
great advantage that it is human readable in its raw form.
JS
Reply | Threaded
Open this post in threaded view
|

Re: Using time and date values

Dennis Cote
John Stanton wrote:

> Brad wrote:
>
>>> I am looking for a simple way of using date and time values with
>>> sqlite3. I have some Ctime classes (VC++.net) which have to be
>>> stored in
>>> the database and they should be compared.
>>
>>
>>
>> If the class you're using has .Value property, or a way to easily
>> transform the date from one format to another, you should probably
>> store that.  For instance, I like to store dates in string format,
>> using something like "CCYYMMDDHHmmSS.SSSSSS". If I expect to need to
>> deal with time zones, I'll store all the dates/times as GMT, or
>> Universal time, and do the conversions when I create the datetime
>> objects.
>>
>>
> You might consider storing dates and times in ISO9601 format, and
> doing as suggested above, using Zulu time (also known as UTC,
> Universal Co-ordinated Time, formerly GMT).  By complying with a
> standard and using UTC you can present the time according to time zone
> and daylight saving status.  In a widely distributed system the
> complication is keeping track of daylight saving, but there are
> databases and tools which help.
>
> The other way to store time is an offset from an epoch.  The problem
> there is that there are arbitrary epochs.  The best one is some date
> BC which has the magic property that it can be used to transform the
> offset into all of the major world date systems, like Gregorian,
> Arabic, Hebrew, Japanese etc.
>
> If you application is fairly straightforward the ISO9601 format has
> the great advantage that it is human readable in its raw form.
> JS
>
This is a good idea, but you have the standard number wrong. It should
be ISO 8601. See
http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html for
more details.

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: Using time and date values

John Stanton-3
Dennis Cote wrote:

> John Stanton wrote:
>
>> Brad wrote:
>>
>>>> I am looking for a simple way of using date and time values with
>>>> sqlite3. I have some Ctime classes (VC++.net) which have to be
>>>> stored in
>>>> the database and they should be compared.
>>>
>>>
>>>
>>>
>>> If the class you're using has .Value property, or a way to easily
>>> transform the date from one format to another, you should probably
>>> store that.  For instance, I like to store dates in string format,
>>> using something like "CCYYMMDDHHmmSS.SSSSSS". If I expect to need to
>>> deal with time zones, I'll store all the dates/times as GMT, or
>>> Universal time, and do the conversions when I create the datetime
>>> objects.
>>>
>>>
>> You might consider storing dates and times in ISO9601 format, and
>> doing as suggested above, using Zulu time (also known as UTC,
>> Universal Co-ordinated Time, formerly GMT).  By complying with a
>> standard and using UTC you can present the time according to time zone
>> and daylight saving status.  In a widely distributed system the
>> complication is keeping track of daylight saving, but there are
>> databases and tools which help.
>>
>> The other way to store time is an offset from an epoch.  The problem
>> there is that there are arbitrary epochs.  The best one is some date
>> BC which has the magic property that it can be used to transform the
>> offset into all of the major world date systems, like Gregorian,
>> Arabic, Hebrew, Japanese etc.
>>
>> If you application is fairly straightforward the ISO9601 format has
>> the great advantage that it is human readable in its raw form.
>> JS
>>
> This is a good idea, but you have the standard number wrong. It should
> be ISO 8601. See
> http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html for
> more details.
>
> HTH
> Dennis Cote
Thankyou for the correction.

Here is how you can get ISO8601 time.
   long      clock;
   char      tmstr[128];
   struct tm *tma;

   clock = time(0);
   tma   = localtime(&clock);
   strftime(tmstr, 128, "%Y-%m-%d", tma);
Reply | Threaded
Open this post in threaded view
|

Re: Using time and date values

emilia12
In reply to this post by John Stanton-3
hi

maybe the GMT is obsolete. The new time-standart becomes BMT
- Biel Mean Time, which is linked up to the Central European
Winter/Standard time - which is UTC + 1 hour (aka internet
time).

regards
e.

Цитат на писмо от John Stanton <[hidden email]>:

> Brad wrote:
> >> I am looking for a simple way of using date and time
> values with
> >> sqlite3. I have some Ctime classes (VC++.net) which
> have to be stored in
> >> the database and they should be compared.
> >
> >
> > If the class you're using has .Value property, or a way
> to easily
> > transform the date from one format to another, you
> should probably store
> > that.  For instance, I like to store dates in string
> format, using
> > something like "CCYYMMDDHHmmSS.SSSSSS". If I expect to
> need to deal with
> > time zones, I'll store all the dates/times as GMT, or
> Universal time,
> > and do the conversions when I create the datetime
> objects.
> >
> >
> You might consider storing dates and times in ISO9601
> format, and doing
> as suggested above, using Zulu time (also known as UTC,
> Universal
> Co-ordinated Time, formerly GMT).  By complying with a
> standard and
> using UTC you can present the time according to time zone
> and daylight
> saving status.  In a widely distributed system the
> complication is
> keeping track of daylight saving, but there are databases
> and tools
> which help.
>
> The other way to store time is an offset from an epoch.
> The problem
> there is that there are arbitrary epochs.  The best one
> is some date BC
> which has the magic property that it can be used to
> transform the offset
> into all of the major world date systems, like Gregorian,
> Arabic,
> Hebrew, Japanese etc.
>
> If you application is fairly straightforward the ISO9601
> format has the
> great advantage that it is human readable in its raw
> form.
> JS
>
>




-----------------------------

Коледа е - всеки заслужава подарък. Тази Коледа с всеки хостинг пакет SuperHosting.BG
Ви подарява книга и дава шанс за спечелване на 3x MP3 плейъра и мобилен телефон.
http://www.superhosting.bg/promo2.adv

Reply | Threaded
Open this post in threaded view
|

Re: Using time and date values

Paul Bohme
In reply to this post by Brad-19
Brad wrote:

>> I am looking for a simple way of using date and time values with
>> sqlite3. I have some Ctime classes (VC++.net) which have to be stored in
>> the database and they should be compared.
>
>
> If the class you're using has .Value property, or a way to easily
> transform the date from one format to another, you should probably
> store that.  For instance, I like to store dates in string format,
> using something like "CCYYMMDDHHmmSS.SSSSSS". If I expect to need to
> deal with time zones, I'll store all the dates/times as GMT, or
> Universal time, and do the conversions when I create the datetime
> objects.


Storing as strings is great as long as you only ever display the
values.  Doing range checks on a stringified format becomes prohibitive,
at best.  If you're going to have to filter/query on the dates, by far
your best bet is a simple number that is an offset from an epoch.  
Classic UNIX calendar time is effective for dates in the proper range
for precisions above one second (most of us) or you can do something
similar to Java's date representation that measures milliseconds since
the epoch.

  -P

Reply | Threaded
Open this post in threaded view
|

Re: Using time and date values

D. Richard Hipp
In reply to this post by Henning Folger
"Henning Folger" <[hidden email]> wrote:
>
> I am looking for a simple way of using date and time values with
> sqlite3.

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

--
D. Richard Hipp <[hidden email]>