datagrid and dates

[kevin11]

Hi,

I have a set of records that contain dates, eg, 2500 BC, 15th March 44BC, 120 AD
Some date are vague, like 2500BC, some are accurate, like 15th March 44BC.
I would like these dates to be displayed in a datagrid column, but also to have them sortable.

LC datetime format doesn't seem suitable, as I believe it thinks the world started in1970 (and I know it started before then because I have some tenuous memories of 1963 and my parents discussing JFK).

What format can I have for dates that make sense when displayed, and will also sort correctly ?

Thanks

Kevin

[kevin11]

or, to refine the question, can I store my dates in a my own format, say, Juian date, but use

on FillInData pDataArray

to convert my dates in something suitable for display ?

In which case, my question becomes, does a conversion for display within "on FillInData" change the underlying data contained by the datagrid, or will the datagrid still contain the original data, suitable for sorting ?

[dunbarx]

I think it would be fun to write a function that calculated dates as you wish. However,

I made a scrollbar and a field "s1". I set the startValue of the scrollbar to -10000. In the scrollBar script:

Code: Select all

on scrollBarDrag var
   put the date into temp
   convert temp to dateItems
   put trunc(the thumbPos of me / 30) into tYear
   put tYear  into item 1 of temp
   
   convert temp to seconds
   put "Seconds:" && temp into line 3 of fld "s1"
   convert temp to dateItems
   put "Converted Date:" && temp into line 1 of fld "s1"
   put "Year:" && tYear into line 2 of fld "s1"
end scrollBarDrag

It seems that in the year 26, it was Tuesday. But the system fails in a couple of ranges. For example, from the year 100 or so up to about 940, you get a negative value (bizarre seconds?). And in the first century, you get an anachronistic mapping to the 20th. Above that 940 you get what seems to be valid dateItem values. Might those weird seconds be used as a counter?

Anyway, I think it would be fun to write your own function, crunching seconds or whatever, and reconstruct the dateItems at ANY date by brute force,

Craig Newman

[BarrySumpter]

...
What format can I have for dates that make sense when displayed, and will also sort correctly ?
...

Date and Time have been an absolute pain in the bum since my first IT assignment.

I like SQLDateTime type format:
YYYYMMDD HH:mm:SS

or much prefer because it so much easier to read in display or in a db:
YYYY-MM-DD HH:mm:SS

2000-01-02 03:04:05

If you maintain that format you can sort on it and it reads really well.


YYYY is four digits from 0000 through 9999 that represent a year.
MM is two digits, ranging from 01 to 12, that represent a month in the specified year.
DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.

hh is two digits, ranging from 00 to 23, that represent the hour.
mm is two digits, ranging from 00 to 59, that represent the minute.
ss is two digits, ranging from 00 to 59, that represent the second.

hth

[BarrySumpter]

How do you convert BC years to AD years?
http://answers.yahoo.com/question/index ... 008AAYmEfY

LOL
I was trying to see if I could fake the sort
leaving my favorite display YYYY-MM-DD HH:mm:ss in tact
but it doesn't look like you can.
The spaces and + and : changes the numic sort into an alpha sort.

So, for me, you can't do what you're asking with one field and its default display.

You have to have a numeric number,
with a preceeding - (numeric negative) meaning BC -YYYYMMDDHHmmSS
and without a preceeding - (numeric positive) meaning AD YYYYMMDDHHmmSS
then have a routine for converting these numbers into displayable dates.

There are some dateDisplay functions built into some databases and spreadsheets.
But these are usually limited to a certain date range. Go figure.

Again for me in liveCode I'd look at:

Convert the date into numerics (no spaces and no +):
-YYYYMMDDHHmmSS
YYYYMMDDHHmmSS

And sort ascending numeric

And format the display in the YYYY-MM-DD HH:mm:SS
with the BC/AD in a different column
or
BC YYYY-MM-DD HH:mm:ss
BC YYYY-MM-DD HH:mm:ss
BC YYYY-MM-DD HH:mm:ss
AD YYYY-MM-DD HH:mm:ss
AD YYYY-MM-DD HH:mm:ss
AD YYYY-MM-DD HH:mm:ss

And DON'T allow "actual" column sorting on the display column.

I fundge a bit and add the numberic column to the datagrid and hide it.
Then trap the column sorting of the display column and sort by the numerc column instead.


It sounds a bit complex reading it like this but with LiveCode the
sorting part and getting the data into a grid is surprisingly simple.

But thats where I'd start.

hth

[kevin11]

Thanks everybody - interesting reading indeed.

The problem with a numeric sort using dates that are negative, is that you have to look at what happens with BC dates. If you sort from smallest number to largest, AD dates work, but BC dates will go wrong - this is because the smaller the negative number, the more recent in history it is, whereas AD dates, it is the larger numbers that are more recent. AD and BC dates have to numerically sort in opposite directions to each other, so you can't just slap a sign in front and sort with -YYYYMMDDHHmmSS

As you can see it sorts wrong, using yyyymmdd below :

-16000308
-16000212
-16000109
-16000108
-16000107
19600108
19700208
19800308

above is sorted numerically smallest to largest but puts 1600 March BC before 1600 January BC, and within January BC puts the day number in the wrong order.

But taking Barry's idea further, I've got a numeric format that seems to work. AD / BC switches the sign, and the left hand side gives the year. The right hand side contains 4 digits that give the month number and day number. However, for negative dates, the month number has to be the number of months before the end of the year (so January is 12), and likewise the day number is the number of days before the end of the month, giving :

-196012 (January 1960 BC)
-196009 (March 1960 BC)
196001 (January 1960 AD)
196004 (March 1960 AD)

Note that in a numeric sort -196012 is smaller than -196009 so comes first, so 12 has to be January in BC dates and December in AD dates.

Rewfining further, use 2 to 13 for month numbers, 2 to 32 for day numbers.
That way if I set the month number to 0, it means the month and day isn't known for the historical event.
If just the day number is set to 0, it means the month is known. So one can then handle 4th March 44 BC as well as March 44 BC, or just 44 BC

And of course, having written all that, I now realise another way would be to just add, say, 10,000 to the original dates to bring all the BC dates into AD (where it numerically sorts correctly without reversing months and days), and then for display just knock off the 10,000 and reformat by sign, etc. !!

Kevin

[BarrySumpter]

LOL

Another perfect example why:

Date and Time have been an absolute pain in the bum since my first IT assignment.

[kevin11]

When I become Emperor we will call that Glorious Day 1. We will then add 1 as each day passes. There will be no months or weekday names, leap years are a complete no-no. There will be 10 hours in a day, and 10 minutes in an hour, etc.

Sorted. (pun intended, of course)

[BarrySumpter]

You've got my vote.