TimeStamp format conversion

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
aebki
Posts: 126
Joined: Tue Jan 06, 2015 5:48 pm

TimeStamp format conversion

Post by aebki » Sat Mar 28, 2015 3:44 pm

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.
Attachments
DDBBTest.sqlite.zip
(243.01 KiB) Downloaded 355 times
myTimeStampConverter.livecode.zip
(27.38 KiB) Downloaded 351 times

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: TimeStamp format conversion

Post by phaworth » Sat Mar 28, 2015 8:42 pm

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

aebki
Posts: 126
Joined: Tue Jan 06, 2015 5:48 pm

Re: TimeStamp format conversion

Post by aebki » Sun Mar 29, 2015 12:17 pm

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.

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: TimeStamp format conversion

Post by Klaus » Sun Mar 29, 2015 2:06 pm

Hi aebki,

please do not get used to (slightly) wrong syntax.
The engine if less forgiving with each new release! 8)

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

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7210
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: TimeStamp format conversion

Post by jacque » Sun Mar 29, 2015 4:45 pm

Only use THE when addressing custom properties:
Or any properties, actually. But you're right about the syntax warning.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9801
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: TimeStamp format conversion

Post by FourthWorld » Sun Mar 29, 2015 5:41 pm

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

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: TimeStamp format conversion

Post by phaworth » Sun Mar 29, 2015 8:40 pm

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

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7210
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: TimeStamp format conversion

Post by jacque » Sun Mar 29, 2015 9:06 pm

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.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: TimeStamp format conversion

Post by phaworth » Sun Mar 29, 2015 11:20 pm

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

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: TimeStamp format conversion

Post by SparkOut » Mon Mar 30, 2015 7:55 am

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.

alvaroabril
Posts: 1
Joined: Sat May 30, 2020 11:16 am

Re: TimeStamp format conversion

Post by alvaroabril » Sat May 30, 2020 11:20 am

Dear Sirs.

Please review my code to convert livecode to timestamp (mySQL format) :D :

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.

matthiasr
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 190
Joined: Sat Apr 08, 2006 7:55 am
Location: Lübbecke, Germany
Contact:

Re: TimeStamp format conversion

Post by matthiasr » Sat May 30, 2020 11:44 am

In line 14 there is a typo

Code: Select all

  delet char 9 to 11 of pTime
should be

Code: Select all

  delete char 9 to 11 of pTime

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: TimeStamp format conversion

Post by Klaus » Sun May 31, 2020 2:25 pm

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

Post Reply

Return to “Databases”