Page 1 of 1
TimeStamp format conversion
Posted: Sat Mar 28, 2015 3:44 pm
by aebki
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.
Re: TimeStamp format conversion
Posted: Sat Mar 28, 2015 8:42 pm
by phaworth
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
Re: TimeStamp format conversion
Posted: Sun Mar 29, 2015 12:17 pm
by aebki
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:
Code: Select all
convert myShortDate from system date to dateItems
I added this:
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
I hope it helps to someone else!
Regards.
Re: TimeStamp format conversion
Posted: Sun Mar 29, 2015 2:06 pm
by Klaus
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:
Code: Select all
...
## put THE item 2 of myShortDate into b
put item 2 of myShortDate into b
...
Here a slightly shortened version of your script:
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
...
Best
Klaus
Re: TimeStamp format conversion
Posted: Sun Mar 29, 2015 4:45 pm
by jacque
Only use THE when addressing custom properties:
Or any properties, actually. But you're right about the syntax warning.
Re: TimeStamp format conversion
Posted: Sun Mar 29, 2015 5:41 pm
by FourthWorld
SQL's pretty common. Why isn't this an option for the convert command?
Re: TimeStamp format conversion
Posted: Sun Mar 29, 2015 8:40 pm
by phaworth
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
Re: TimeStamp format conversion
Posted: Sun Mar 29, 2015 9:06 pm
by jacque
Another way:
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
Edit: made it shorter.
Re: TimeStamp format conversion
Posted: Sun Mar 29, 2015 11:20 pm
by phaworth
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
Re: TimeStamp format conversion
Posted: Mon Mar 30, 2015 7:55 am
by SparkOut
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.
Re: TimeStamp format conversion
Posted: Sat May 30, 2020 11:20 am
by alvaroabril
Dear Sirs.
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
Re: TimeStamp format conversion
Posted: Sat May 30, 2020 11:44 am
by matthiasr
In line 14 there is a typo
should be
Re: TimeStamp format conversion
Posted: Sun May 31, 2020 2:25 pm
by Klaus
Hola Alvaro,
welcome to the forum!
1. What Matthias said!
2. We are VERY informal here, so no SIR etc. neccessary, my friend!
alvaroabril wrote: ↑Sat May 30, 2020 11:20 am
Dear Sirs.
...
Best
Klaus