Skip to content

Adding TimeStamp to the SQL Database and linking JQPlot to this data

May 16, 2012

So last week, I had seen that JQPlot can work nicely for Google Chrome if it is integer values being passed in through JSON or via the Web Response using ASP.NET, due to some unknown bug in the browser after displaying the first JQPlot graph.

In order to do the UNIX TimeStamp as an integer, it would be great to store the field in the database for direct accessible use, this is the script code I had come up with for storing the UNIX TimeStamp in SQL: –

DECLARE @date DATETIME UPDATE [DatesDB].[dbo].[Dates] SET TimeStamp = DATEDIFF(s, ‘1970-01-01 00:00:00’, CAST(       CAST([year] AS VARCHAR(4)) +       RIGHT(‘0’ + CAST([month] AS VARCHAR(2)), 2) +       RIGHT(‘0’ + CAST([day] AS VARCHAR(2)), 2)    AS DATETIME))

As you can see it casts the year, month and day values from the fields that are already in my database for that table, and sets the date difference to calculate the UNIX TimeStamp (number of seconds), and it calls CAST and RIGHT function for localisation of the time being set.

In the code behind for the JQPlot Graph, I had used the string variable Data to store all the string data to be stored on Page Load with the following code snippet: –

Data = “[“;

IList<Dates> dates= Dates.GetDates(userName);

for (int i = 0; i < dates.Count; i++)

{

               string dateString = “[“ + dates[i].TimeStamp + “000” + “, “;

               string pointString = dates[i].Count.ToString() + “]”;

                Data += dateString + pointString;

               if (i != dates.Count – 1)

                {

                    Data += “, “;

                }

         }

Data += “]”;

This is reinforcing the code I had wrote last week, as you can see I created a timestamp field in the database to cater the UNIXTimeStamp field which is stored in the table Dates. This didn’t work as I had anticipated straight away after a few tries, therefore I had plugged in the value “000” after the timestamp, as this denotes the total seconds in the UNIX TimeStamp.

This had worked for the browsers Google Chrome, Mozilla Firefox, and Internet Explorer 9.

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: