TimeStamp format conversion
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
TimeStamp format conversion
Hi,
I need to use some old information i have in one ms access database.
Here i have stored date in the format: "dd/mm/yyyy"
Now i imported this data to one SQLite database, and i need to store these dates in timestamp format.
So i made one little application to convert this data. I attached the stack i am using (The stack has three columns, and in the beginning the third column is empty. So once i read and i convert the date format, then i am filling this third column) .
It seems that is working well the conversion, but then i am making one "Select * from test_table order by myDateTimeStamp desc"
And i can see that the order is not the correct. So i imagine that i am making a wrong conversion of the dates.
Someone can tell me where is my mistake?
Kind regards.
I need to use some old information i have in one ms access database.
Here i have stored date in the format: "dd/mm/yyyy"
Now i imported this data to one SQLite database, and i need to store these dates in timestamp format.
So i made one little application to convert this data. I attached the stack i am using (The stack has three columns, and in the beginning the third column is empty. So once i read and i convert the date format, then i am filling this third column) .
It seems that is working well the conversion, but then i am making one "Select * from test_table order by myDateTimeStamp desc"
And i can see that the order is not the correct. So i imagine that i am making a wrong conversion of the dates.
Someone can tell me where is my mistake?
Kind regards.
- Attachments
-
- DDBBTest.sqlite.zip
- (243.01 KiB) Downloaded 387 times
-
- myTimeStampConverter.livecode.zip
- (27.38 KiB) Downloaded 377 times
Re: TimeStamp format conversion
Looks like your conversion is not creating the correct SQL timestamp format. I think you're setting it to the dateItems format which is not recognized by SQL.
The SQL timestamp format is:
YYYY-MM-DD HH:MM:SS
There's a space between the date and the time - it can also be the letter "T"
Pete
The SQL timestamp format is:
YYYY-MM-DD HH:MM:SS
There's a space between the date and the time - it can also be the letter "T"
Pete
Re: TimeStamp format conversion
Hi Phaworth,
Thanks for your answer!:-)
And yes, you are right!
Anyway for my problem was the same the timestamp format or the dateItems, as what i was not managing concerned the order.
Finally i could resolve it like this.
after this line i have in the code of my button:
I added this:
I hope it helps to someone else!
Regards.
Thanks for your answer!:-)
And yes, you are right!
Anyway for my problem was the same the timestamp format or the dateItems, as what i was not managing concerned the order.
Finally i could resolve it like this.
after this line i have in the code of my button:
Code: Select all
convert myShortDate from system date to dateItems
Code: Select all
if the number of chars in item 2 of myShortDate is 1 then
put 0 into a
put the item 2 of myShortDate into b
put b after a
put a into item 2 of myShortDate
end if
if the number of chars in item 3 of myShortDate is 1 then
put 0 into a
put the item 3 of myShortDate into b
put b after a
put a into item 3 of myShortDate
end if
Regards.
Re: TimeStamp format conversion
Hi aebki,
please do not get used to (slightly) wrong syntax.
The engine if less forgiving with each new release!
Only use THE when addressing custom properties:
Here a slightly shortened version of your script:
Best
Klaus
please do not get used to (slightly) wrong syntax.
The engine if less forgiving with each new release!
Only use THE when addressing custom properties:
Code: Select all
...
## put THE item 2 of myShortDate into b
put item 2 of myShortDate into b
...
Code: Select all
...
if the number of chars OF item 2 of myShortDate = 1 then
put 0 BEFORE item 2 of myShortDate
end if
if the number of chars OF item 3 of myShortDate = 1 then
put 0 BEFORE item 3 of myShortDate
end if
...
Klaus
-
- VIP Livecode Opensource Backer
- Posts: 7257
- Joined: Sat Apr 08, 2006 8:31 pm
- Location: Minneapolis MN
- Contact:
Re: TimeStamp format conversion
Or any properties, actually. But you're right about the syntax warning.Only use THE when addressing custom properties:
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com
HyperActive Software | http://www.hyperactivesw.com
-
- VIP Livecode Opensource Backer
- Posts: 9848
- Joined: Sat Apr 08, 2006 7:05 am
- Location: Los Angeles
- Contact:
Re: TimeStamp format conversion
SQL's pretty common. Why isn't this an option for the convert command?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
Re: TimeStamp format conversion
Good question Richard! I believe there is more than one QCC enhancement request for this, although I don't have the report numbers in front of me.
Pete
Pete
-
- VIP Livecode Opensource Backer
- Posts: 7257
- Joined: Sat Apr 08, 2006 8:31 pm
- Location: Minneapolis MN
- Contact:
Re: TimeStamp format conversion
Another way:
Edit: made it shorter.
Code: Select all
function sqlDate pDate
convert pDate to dateitems
set the numberformat to "00"
return merge("[[item 1 of pDate]]-[[item 2 of pDate + 0]]-[[item 3 of pDate + 0]]")
end sqlDate
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com
HyperActive Software | http://www.hyperactivesw.com
Re: TimeStamp format conversion
Glad you got it working.
Will your users be adding more records to this database after you have converted it? If so, you might want to think about using the standard SQL timestamp format.
You can define the myDateTimeStamp column with DEFAULT CURRENT_TIMESTAMP. When you INSERT a new record, don't include myDateTimeSTamp in the list of columns and SQLite will automatically set the timestamp value in it.
One less thing to do in your Livecode scripts
Pete
Will your users be adding more records to this database after you have converted it? If so, you might want to think about using the standard SQL timestamp format.
You can define the myDateTimeStamp column with DEFAULT CURRENT_TIMESTAMP. When you INSERT a new record, don't include myDateTimeSTamp in the list of columns and SQLite will automatically set the timestamp value in it.
One less thing to do in your Livecode scripts
Pete
Re: TimeStamp format conversion
True, and also in an update you can use NOW() as the value in the SQL query (not supplied from your data) and it will update the timestamp column for that record with the time set from the server. At least on MySQL. SQLite might have a different syntax.
-
- Posts: 1
- Joined: Sat May 30, 2020 11:16 am
Re: TimeStamp format conversion
Dear Sirs.
Please review my code to convert livecode to timestamp (mySQL format) :
Please review my code to convert livecode to timestamp (mySQL format) :
Code: Select all
on mouseup
put the date into pDate
convert pDate to dateitems
set the numberformat to "00"
put merge("[[item 1 of pDate]]-[[item 2 of pDate + 0]]-[[item 3 of pDate + 0]]") into pDate
put the long time into pTime
put the number of chars of pTime into numbers
if numbers = 10 then
delete char 8 to 10 of pTime
put "0"&pTime into pTime
else
delete char 9 to 11 of pTime
end if
put pDate && pTime into pDate
put pDate into msg
end mouseup
Last edited by alvaroabril on Tue Jun 09, 2020 7:37 am, edited 1 time in total.
-
- VIP Livecode Opensource Backer
- Posts: 190
- Joined: Sat Apr 08, 2006 7:55 am
- Location: Lübbecke, Germany
- Contact:
Re: TimeStamp format conversion
In line 14 there is a typo
should be
Code: Select all
delet char 9 to 11 of pTime
Code: Select all
delete char 9 to 11 of pTime
Re: TimeStamp format conversion
Hola Alvaro,
welcome to the forum!
1. What Matthias said!
2. We are VERY informal here, so no SIR etc. neccessary, my friend!
Klaus
welcome to the forum!
1. What Matthias said!
2. We are VERY informal here, so no SIR etc. neccessary, my friend!
Best
Klaus