18 Responses for "How do I make silly graphs?"

1. Anonymous January 30th, 2007 at 2:03 1

   They are simple UNIX timestamps: the number of seconds since 1970 Jan 1.

   There is a sucky converter at unixtimestamp .com; or you can just divide it by (60*60*24*365) and add 1970 to get the year and so on…

2. Beowulf Lee January 30th, 2007 at 2:08 2

   >>>172 = Misuzu
   173 = Suigintou

   These numbers are scaring me. What do they mean.

3. Anonymous January 30th, 2007 at 2:16 3

   @Beowulf Lee

   They mean who the numbers in the DB correspond to. Don’t worry Misuzu won! :)~

4. Beowulf Lee January 30th, 2007 at 2:23 4

   >>>They mean who the numbers in the DB correspond to. Don’t worry Misuzu won! :)~

   Oh OK. If by win, you mean “win”, then I guess so. It’s not that I’m not glad, it’s just that I didn’t want it to be like this.

   Though, when/if abq figures this thing out, the graph should be interesting.

5. Anonymous January 30th, 2007 at 2:31 5

   if you have open office you can import from the database:
   http://grass.gdf-hannover.de/wiki/Openoffice.org_with_SQL_Databases#MySQL_.28OpenOffice.org_2.x.29

   then graph from there

6. Anonymous January 30th, 2007 at 2:34 6

   duplicate the table to wp_pollsip_duplicate, then run “UPDATE wp_pollsip_duplicate SET pollip_timstamp=FROM_UNIXTIME(pollip_timestamp-3600);” to fix the timestamps.

   (change 3600 to match your timezone, +/- 3600 seconds per hour)

7. CRV§ADER//KY January 30th, 2007 at 2:56 7

   duplicate the table to wp_pollsip_duplicate, then run “UPDATE wp_pollsip_duplicate SET pollip_timstamp=FROM_UNIXTIME(pollip_timestamp-3600);” to fix the timestamps.

   (change 3600 to match your timezone, +/- 3600 seconds per hour)

   DON’T
   as ALL timestamps in a SQL database are recorded in UNIX time, doing so will break them in some horrible way.

   Instead, whatever software you’re using (including excel), you’ll find a lot of functions to convert from unix timestamps to readable dates for your printing/viewing purposes. just browse the function catalog under “date and time” or something.

8. CRV§ADER//KY January 30th, 2007 at 2:57 8

   P.S. any clues on why if I insert my website (http://cr!usaderky.alter!vista.org, removing the !) I get a “pending moderator approval” notice?

9. CRV§ADER//KY January 30th, 2007 at 3:07 9

   a simple way to retrieve a readable output is

   SELECT pollip_qid,pollip_aid,FROM_UNIXTIME(pollip_timestamp) FROM wp_pollsip;

   note that I didn’t apply any timezone correction, as that is already done by FROM_UNIXTIME() (see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html), nor I saved the formatted output back into the pollip_timestamp field, as it’s really not recommended for integrity reasons (you don’t want to find yourself with half of the DB in readable format and half in UNIX timestamps).

   yet it’s odd… the pollip_timestamp is declared as varchar, but it’s actually saved as a UNIX timestamp (as it’s supposed to be). I smell poor programming here.

10. CRV§ADER//KY January 30th, 2007 at 3:11 10

    BTW, what kind of graphs do you want to do? if you want to extract the sums, you just have to

    SELECT COUNT(pollip_timestamp) FROM wp_pollsip GROUP BY pollip_aid WHERE pollip_qid=’54′;

    (note: I didn’t test this one for grammar, but it should work fine)

11. mwgamera January 30th, 2007 at 3:13 11

    It looks easy, even with the data you provided.
    I would do this this way:

    1. Get times sorted ascending (records are alredy sorted so just revers the whole file with tac).
    2. Simple perl script (like this one: http://phpfi.com/198770) to convert it to better format.
    3. Use gnuplot (for example with this: http://phpfi.com/198773)
    4. The result: http://img441.imageshack.us/img441/6742/129ux2.png

12. Anonymous January 30th, 2007 at 3:19 12

    my saimoe ripoff:

    http://img260.imageshack.us/img260/8848/graph2tj5.png

    >>CRV§ADER//KY

    note i said “duplicate the table” and fix the timestamps in the duplicate table…

13. Anonymous January 30th, 2007 at 3:24 13

    Lol @ how oddly consistant both are throughout.

14. Anonymous January 30th, 2007 at 4:24 14

    Using Excel isn’t ideal, but it can be done (I used it for the data in the Suiseiseki battle). You’ll need to convert the timestamps though, as suggested above. Despite what CRV§ADER//KY said, Excel has no native support for unixtime; someone may have written a function for it, but I find converting the data before I even get the CSV file simpler.

    You’ll need to change the x-axis scale type to “category” to show the voting density and dispersion.

15. abq January 30th, 2007 at 4:33 15

    Thanks for the info guys. I’m gonna sit down with it when I have time and see if I can get any wiser.

    P.S. any clues on why if I insert my website (http://cr!usaderky.alter!vista.org, removing the !) I get a “pending moderator approval” notice?

    My spam filter has a life of its own. altervista.org is apparently on its blacklist. Don’t ask me why. Can’t live with it, can’t live without it.

    Didn’t you get the chance to solve a captcha?

16. Anonymous January 30th, 2007 at 9:21 16

    abq, are you going to keep shinku? well… we all know this bot crap is gonna happen again.

17. lolitron :: (*´Д`)ﾊｧﾊｧ January 31st, 2007 at 2:23 17

    […] 01/30: How do I make silly graphs? […]

18. CRV§ADER//KY January 31st, 2007 at 12:36 18

    My spam filter has a life of its own. altervista.org is apparently on its blacklist. Don’t ask me why.
    Errr. altervista.org is a host keeping, like, one or two MILLION private websites. -_-

    Didn’t you get the chance to solve a captcha?
    nope.