Month string from yyyy-mm-dd

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

Month string from yyyy-mm-dd

RB Smissaert
Is it possible with the date-time functions to get the month as a string, so
January etc. from the date in the format yyyy-mm-dd?
I can get the month as a number like this:
select strftime('%m', '2007-02-17')
and I could do a table update by joining to a month lookup table, but I
wonder if there is a simpler way to do this.
Thanks for any advice.

RBS



-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Month string from yyyy-mm-dd

Martin Jenkins-2
RB Smissaert wrote:
> Is it possible with the date-time functions to get the month as a string, so
> January etc. from the date in the format yyyy-mm-dd?

Doesn't look like it. Nothing in the wiki and I couldn't see anything in
the source either. I suppose you could use a big case statement if you
wanted to avoid joining with a month table.

Martin

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: Month string from yyyy-mm-dd

RB Smissaert
Yes, it looks it isn't there.
It is no problem though to update the table and 25000 records takes about a
third of a second when I do it from the integer yyyymmdd format like this:

UPDATE A2IDB3F_J SET DATE_OF_BIRTH =
(SELECT MONTH_TEXT FROM MONTH_LOOKUP WHERE
(SELECT CAST(DATE_OF_BIRTH / 100 AS INTEGER) -
CAST(DATE_OF_BIRTH / 10000 AS INTEGER) * 100) = MONTH_NUMBER)

Will see if doing it with a substr function is any faster.
Would that work on integer numbers?

RBS

-----Original Message-----
From: Martin Jenkins [mailto:[hidden email]]
Sent: 18 February 2007 18:53
To: [hidden email]
Subject: Re: [sqlite] Month string from yyyy-mm-dd

RB Smissaert wrote:
> Is it possible with the date-time functions to get the month as a string,
so
> January etc. from the date in the format yyyy-mm-dd?

Doesn't look like it. Nothing in the wiki and I couldn't see anything in
the source either. I suppose you could use a big case statement if you
wanted to avoid joining with a month table.

Martin

----------------------------------------------------------------------------
-
To unsubscribe, send email to [hidden email]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Month string from yyyy-mm-dd

D. Richard Hipp
In reply to this post by RB Smissaert
"RB Smissaert" <[hidden email]> wrote:
> Is it possible with the date-time functions to get the month as a string, so
> January etc. from the date in the format yyyy-mm-dd?

This is difficult to internationalize so I omitted it in my
implementation of the date/time functions.  You can, of course,
grab the source to the date/time functions, make what modifications
you want, and use the modified date/time functions in your code.
But text month names are not supported by the core distribution
of SQLite.
--
D. Richard Hipp  <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: Month string from yyyy-mm-dd

RB Smissaert
Thanks, I thought that would be the reason it wasn't included.
Doing a join with a lookup table is very much fast enough, so there is no
problem there.

Just tried it with substr instead of integer division (starting with
yyyymmdd) and they are equally fast. Will now try a big CASE statement, but
I somehow guess it will be slower.

RBS

-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: 18 February 2007 19:37
To: [hidden email]
Subject: Re: [sqlite] Month string from yyyy-mm-dd

"RB Smissaert" <[hidden email]> wrote:
> Is it possible with the date-time functions to get the month as a string,
so
> January etc. from the date in the format yyyy-mm-dd?

This is difficult to internationalize so I omitted it in my
implementation of the date/time functions.  You can, of course,
grab the source to the date/time functions, make what modifications
you want, and use the modified date/time functions in your code.
But text month names are not supported by the core distribution
of SQLite.
--
D. Richard Hipp  <[hidden email]>


----------------------------------------------------------------------------
-
To unsubscribe, send email to [hidden email]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: Month string from yyyy-mm-dd

RB Smissaert
Analyzing the lookup table knocks the time down from 0.36 to 0.31 secs,
something I didn't expect.

RBS

-----Original Message-----
From: RB Smissaert [mailto:[hidden email]]
Sent: 18 February 2007 19:59
To: [hidden email]
Subject: RE: [sqlite] Month string from yyyy-mm-dd

Thanks, I thought that would be the reason it wasn't included.
Doing a join with a lookup table is very much fast enough, so there is no
problem there.

Just tried it with substr instead of integer division (starting with
yyyymmdd) and they are equally fast. Will now try a big CASE statement, but
I somehow guess it will be slower.

RBS

-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: 18 February 2007 19:37
To: [hidden email]
Subject: Re: [sqlite] Month string from yyyy-mm-dd

"RB Smissaert" <[hidden email]> wrote:
> Is it possible with the date-time functions to get the month as a string,
so
> January etc. from the date in the format yyyy-mm-dd?

This is difficult to internationalize so I omitted it in my
implementation of the date/time functions.  You can, of course,
grab the source to the date/time functions, make what modifications
you want, and use the modified date/time functions in your code.
But text month names are not supported by the core distribution
of SQLite.
--
D. Richard Hipp  <[hidden email]>


----------------------------------------------------------------------------
-
To unsubscribe, send email to [hidden email]
----------------------------------------------------------------------------
-




----------------------------------------------------------------------------
-
To unsubscribe, send email to [hidden email]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Month string from yyyy-mm-dd

Martin Jenkins-2
In reply to this post by RB Smissaert
RB Smissaert wrote:
> Yes, it looks it isn't there.

I guess it isn't really SQLite's place to know how to spell this month's
name in your locale. It's a presentation issue at the end of the day and
a lookup table is a nice easy solution.

> to update the table and 25000 records takes about a third of a second
 > ...
> Will see if doing it with a substr function is any faster.
> Would that work on integer numbers?

Dunno, but 80,000 updates a second seems quite good to me. How often do
you need to run this? Premature/excessive optimisation and all that. ;)

Martin

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Month string from yyyy-mm-dd

Martin Jenkins-2
In reply to this post by RB Smissaert
RB Smissaert wrote:
> Analyzing the lookup table knocks the time down from 0.36 to 0.31 secs,
> something I didn't expect.

That shows the importance of testing. I ported the SQLite benchmarks to
Python and was surprised to see some of the tests taking minutes to run
versus a few (or a few tens of) seconds for the SQLite shell reading SQL
from a file or the same wrapper executing the statements individually.

For a 100k line bulk insert (e.g., restoring from a .dump to a disk
file) that could mean a slow down from about 25 seconds to over 5500
seconds, or 92 minutes...

Martin

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------