Date formatting/template program mode

[bookworm2000]

Hi everyone,

this is my first post, thank you for taking the time to look into my problem.

Which is...

I use a custom column to store the publishing dates of magazines. These come in various formats like

• 1955.04.20 (most exact)
• 1955.04
• 1955
• 1955.Spring
• 1955.Spring/Summer (worst case)

So I use strings instead of dates or numbers.

These of course don't sort exactly but can easily be found as hierarchical items in the tag browser.

Now I want to extract a formatted date from this data:

1955.04.20
should be
1955-04-20

1955.04
should be
1955-04-00

1955
should be
1955-00-00

1955.Spring
should be
1955-00-00

1955.Spring/Summer
should be
1955-00-00

(the season data should be omitted completely).

What I have so far:

3 (hidden) columns to isolate year, month and day:
{#original_date:subitems(0,1)}
{#original_date:subitems(1,2)}
{#original_date:subitems(2,3)}

and a fourth to build the formatted date:
{#original_year:ifempty(0000)}-{#original_month:ifempty(00)}-{#original_day:ifempty(00)}

What I want to achieve:

1. get them all into one column
2. check the month for non-integer data and replace it with "00"

So far I haven't wrapped my brain around the template program mode, I don't find a way to combine subitems() and ifempty().

Thanks in advance!

[Sabardeyn]

Ok, I'm not the best person to respond, particularly as I have not dabbled in templates and don't otherwise see the need for them. It could be my own ignorance though.

Anyway... That seems like an awful lot of work to produce 'useless' intermediary data. I say 'useless' because if the year, month and day columns are not otherwise needed, you're storing data unnecessarily.


I've just found out about subgroups and think they might be of use in this instance: http://manual.calibre-ebook.com/sub_groups.html

You might be able to create a single custom column with a data format of: YYYY.MM.DD.Q, making use of numbers with leading zeros for all segments except the last. The format is pretty much standard date field notation, adding a "Quarter #" into the mix. The only thing that would be odd is the quarterly / seasonal releases will be slightly out of true placement among other data - because day "00" is going to appear at the beginning of any listings for the month.

You could also do a YYYY.DDD format where days are # of days in the year (ex: Dec 25, 2012 = 2012.359). While accurate, this latter format is not as readable / user friendly.

Using subgroups might have the added benefit that you could output / display all of the publications in your database released in Oct 1953, for instance. Searching on #custom_column_name:"1953.10".


I'm not sure if these ideas are what you need, but I hope I've helped. (Or at least prompted someone else to jump in and offer a more viable suggestion!)

[chaley]

I meant to answer this sometime back, but got busy.

The following general program mode template does what I think you want.

program:
	date = field('#original_date');
# compute the components
	year = subitems(date, 0, 1);
	month = re(subitems(date, 1, 2), '^\D*$',  '00');
	day = ifempty(subitems(date, 2, 3), '00');
# compute the return value
	strcat(year, '--', month, '--', day)

The trickiest part is the call to re. The pattern matches if the source contains only non-numeric characters, replacing them with '00' otherwise leaving them alone.

[bookworm2000]

Thank you both for your answers.

Originally Posted by Sabardeyn

I've just found out about subgroups and think they might be of use in this instance: http://manual.calibre-ebook.com/sub_groups.html

The column containing the original data is already organized into subgroups.

Originally Posted by chaley

The following general program mode template does what I think you want.

program:
	date = field('#original_date');
# compute the components
	year = subitems(date, 0, 1);
	month = re(subitems(date, 1, 2), '^\D*$',  '00');
	day = ifempty(subitems(date, 2, 3), '00');
# compute the return value
	strcat(year, '--', month, '--', day)

That's exactly what I was looking for.



One last question:

I expanded the template to ISO 8601 format. Is there a way to convert this date string to datetime data type?

That would allow me to

a) sort the column by date instead of string,
b) use date formatting in further columns (e.g. used for display in the content server).

Or is that impossible because 1955-00-00T00:00:00Z+00:00 is not a valid date (with a 00 month and day)?

[chaley]

A date column must contain a valid date. That is why calibre adds month and day to dates that display only a year.

Do note that ISO dates sort correctly even as strings.