RE: [sqlite] Time zone conversion
Let the time zone given by user +0530 Therefore its 330 minutes / 13200 seconds select datetime( StartTime,'unixepoch','+13200.0 seconds') from mytable --- gives you the local time Select datetime( StartTime,'unixepoch')gives u universal time Where StartTime is an integer(unixtimestamp stored in the database) With Regards Karthick V "Don't bother just to be better than your contemporaries or predecessors. Try to be better than yourself" William Faulkner -Original Message- From: Lloyd [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 20, 2006 10:30 AM To: sqlite-users@sqlite.org Subject: [sqlite] Time zone conversion Hi, I would like to implement a time zone independent function. So I get the time zone information from user, and plans to convert the datetime to the time zone specified. To make it clear I give an example here StartTime is stored as an integer in the database select datetime(StartTime,'unixepoch') from mytable; will give the universal time. To get the local time, what I do is- Let the time zone given by user +0530 convert +0530 to seconds, =13200 ((5*60+30)*60), then select datetime(StartTime+13200,'unixepoch') from mytable; But it is not working as I expected. (no result is shown) what could be the reason? Is there any better way for me to achieve the same result? Thanks and Regards, Lloyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - DISCLAIMER The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Time formatting
Hi Dennis,
Thanks for the code, made me think of an alternate approach.
Tried out your code and made some changes. Here is the updated version
select strftime('%Y-%m-%dT%H:%M:%f', localtime) ||
case
when (strftime('%s',localtime) - strftime('%s',GMTTime)) > 0
then '+' || strftime('%H:%M', strftime('%s',localtime) -
strftime('%s',GMTTime),'unixepoch')
else '-' || strftime('%H:%M', strftime('%s',GMTTime) -
strftime('%s',localtime),'unixepoch')
end
from twotimes
Works fine for any time zone.
With Regards
Karthick V
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 28, 2006 11:04 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Time formatting
Karthick V - TLS , Chennai wrote:
> Hi everyone,
>
> I am trying to generate a time information with the time zone offset
> information in the UTC format
>
> -MM-DDTHH:MM:SS.mmm+HH:MM
>
>
Try this:
select strftime('%Y-%m-%dT%H:%M:%f', 'now', 'localtime') ||
case
when strftime('%H:%M', '12:00', 'localtime') < '12:00'
then strftime('-%H:%M', '00:00', 'utc')
else strftime('+%H:%M', '00:00', 'utc')
end;
When I run this here I get:
2006-11-28T10:29:51.091-07:00
Which is the correct timezone offset for MST.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended
for the
named recipient(s) only. It shall not attach any liability on the originator or
HCL or its
affiliates. Any views or opinions presented in this email are solely those of
the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of
reproduction,
dissemination, copying, disclosure, modification, distribution and / or
publication of this
message without the prior written consent of the author of this e-mail is
strictly
prohibited. If you have received this email in error please delete it and
notify the sender
immediately. Before opening any mail and attachments please check them for
viruses and
defect.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Time formatting
Hi everyone,
I am trying to generate a time information with the time zone offset
information in the UTC format
-MM-DDTHH:MM:SS.mmm+HH:MM
with the last HH and MM referring to the offset of the timezone hours and
minutes from the GMT. My table is created using the following queries
CREATE TABLE TwoTimes(localTime datetime, GMTTime datetime);
Rows are inserted using the following sql statement
insert into twoTimes(localtime,gmttime)
values(strftime('%Y-%m-%dT%H:%M:%f','now','localtime'),strftime('%Y-%m-%dT%H
:%M:%f','now','utc','+5 minutes'));
I have written a query of this type to get most of the information.
select strftime('%Y-%m-%dT%H:%M:%f',localtime) ||
quote((strftime('%s',localtime) - strftime('%s',gmttime))/3600) || ':' ||
quote((abs((strftime('%s',localtime) - strftime('%s',gmttime)))%3600)/60)
from twotimes
I have the problem of getting the "+" sign ('-' comes through the
calculation itself) and having two digit Hours and minutes for the timezone
offset.
Anyone has done a similar method of implementing.
With Regards
Karthick V
HCL Technologies Limited,
Chennai, India
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended
for the
named recipient(s) only. It shall not attach any liability on the originator or
HCL or its
affiliates. Any views or opinions presented in this email are solely those of
the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of
reproduction,
dissemination, copying, disclosure, modification, distribution and / or
publication of this
message without the prior written consent of the author of this e-mail is
strictly
prohibited. If you have received this email in error please delete it and
notify the sender
immediately. Before opening any mail and attachments please check them for
viruses and
defect.
RE: [sqlite] Re: Date and time comparison
Thanks Igor. It works fine.
With Regards
Karthick V
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Friday, November 17, 2006 6:37 PM
To: SQLite
Subject: [sqlite] Re: Date and time comparison
Karthick V - TLS , Chennai <[EMAIL PROTECTED]>
wrote:
> I need to get the row id for a time which falls within the start and
> end time.
>
> I am using this query.
>
> select RowID,
> strftime('%Y-%m-%dT%H:%M:%S',starttime),
> strftime('%Y-%m-%dT%H:%M',endtime) from History where
> strftime('%Y-%m-%dT%H:%M',starttime) <
> strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17') and
> strftime('%Y-%m-%dT%H:%M',endtime) >
> strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17');
>
> This works well if there is only one matching row.
>
> However if more than one row matches, I need to get the row whose
> start time is closest to the given time.
Just add
order by strftime('...', starttime) desc limit 1;
Observe that, since all starttimes are before the given time, the nearest
one is also the latest one.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended
for the
named recipient(s) only. It shall not attach any liability on the originator or
HCL or its
affiliates. Any views or opinions presented in this email are solely those of
the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of
reproduction,
dissemination, copying, disclosure, modification, distribution and / or
publication of this
message without the prior written consent of the author of this e-mail is
strictly
prohibited. If you have received this email in error please delete it and
notify the sender
immediately. Before opening any mail and attachments please check them for
viruses and
defect.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Date and time comparison
Hello everyone,
I am trying to retrieve some records from the database using date and time
comparison. The table has three columns, Row Id, Start Time and end Time.
I need to get the row id for a time which falls within the start and end
time.
I am using this query.
select RowID,
strftime('%Y-%m-%dT%H:%M:%S',starttime),
strftime('%Y-%m-%dT%H:%M',endtime) from History where
strftime('%Y-%m-%dT%H:%M',starttime) <
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17') and
strftime('%Y-%m-%dT%H:%M',endtime) >
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17');
This works well if there is only one matching row.
However if more than one row matches, I need to get the row whose start time
is closest to the given time. Say the start times are 12:10 and 12:20 and
the given time is 12:25, it should return the second record only.
I am trying the following query
strftime('%Y-%m-%dT%H:%M',endtime - starttime)
however this fails giving some negative numbers.
If anyone knows of a solution, kindly post a reply for this. Thanks in
advance.
With Regards
Karthick V
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended
for the
named recipient(s) only. It shall not attach any liability on the originator or
HCL or its
affiliates. Any views or opinions presented in this email are solely those of
the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of
reproduction,
dissemination, copying, disclosure, modification, distribution and / or
publication of this
message without the prior written consent of the author of this e-mail is
strictly
prohibited. If you have received this email in error please delete it and
notify the sender
immediately. Before opening any mail and attachments please check them for
viruses and
defect.
