is there some forumla or website to convert dates as they are written in mysql tables to real dates and back again? I need to add a ton of stuff to my calendars.. I dont even know what the format is called..
For instance the start date for one event is April 16 2012, but it shows inthe DB as
1326766314
if I could convert them and upload in bulk it would be so much easier..
one more quick search and this time I found it
http://www.onlineconversion.com/unix_time.htm
php has a built in date function for this, you know.
aw crap really? you mean I could have just enetered the date as 6/15/2012 in that column???? I did get it to work though with the forumla
well FYI I did get some 60-odd events added in in an hour.. pretty good I think
forumla or no formula
but good to know because I know there are a ton more to be added
You could convert it using something simple like, lets say, we convert user's registration date in plain date format, then you could use something like:
($mybb->user['regdate'])/(60*60*24)
So that is user's regdate divided by 60 (60 seconds in a minute) X 60 (60 minutes in an hour) X 24 (24 hours a day). This simple calculation can take you to the way of getting the actual date most prolly, you can always make a try if that works, but that is how it works I believe.
edit:
aw snap;
leave that, that is for subtracting out the time differentiation and outputting it, you can use something like this:
'. my_date($mybb->settings['dateformat'],$variable['dateline']) .'
So above code would parse out into readable date format just as we see.
I'm 90% sure that the number in the database is just the number of seconds since
Midnight (UTC), January 1st, 1970. PHP does have
functions for getting a date/time from this.
// Writes the current date e.g. Tuesday, January 17th, 2012
// Just replace time() with a variable containing the number
// from the database and you're set to go.
// The second link in my post shows how I got those letters
echo date("l, F jS, Y", time());
Going the other way is a bit more complicated, and having never used it before I can't give you an example I know works.
timestamps in the database are in unix time relative to UTC. You can get that data into a human readable format based on the current system timezone (converted internally during conversion) via
SELECT FROM_UNIXTIME(timestamp_field, <optional format>) FROM tablename
if you want to update the DB with a timestamp of a specific date use
UDPATE tablename SET timestamp_field = UNIX_TIMESTAMP(date-time) WHERE XXXXX
however, this assumes that the date-time variable is in the current system timezone and converts to UTC internally during the conversion
I simply did a caluclation in excel to convert from excel to unix time.. yes the 1/1/1970 is what it works off of.. then there is the matter of that being at GMT not my time zone.. so I put the actual dates in the far columns, did the calucations in the correct columns:
=(CELLL_REF-"1/1/1970")*60*60*24
and it worked perfect
COL A - Event ID (used sequential fill to get this filled in)
COL B - Calendar ID (Edit Fill Down)
COL C - User ID (mine Edit Fill Down)
COL D - Event Name
COL E - Event Details
COL F - Visible (set to 1 Edit Fill Down)
COL G - Private: (set to 0 Edit Fill Down)
COL H - Entry Date (Edit Fill Down cause it was no big deal what that was)
COL I - Start Date (used that calculatio to call the actual date from COL O)
COL J - END Date (used that calculatio to call the actual date from COL P)
COL K - Time Zone (set to 0 Edit Fill Down)
COL L - Ignore TZ (set to 0 Edit Fill Down)
COL M - Time (our events are all all-day so I dont use that, set to 0 Edit Fill Down)
COL N - Repeat (N if sngle day, a:1:{s:7:"repeats";i:0;} if it spaned days - trade shows usually span days)
COL O - the actual Date event starts
COL P - the actual Date event ends (blank is single day event)
I use
http://www.tsnn.com and industry sites to search for shows in my industry and copy.paste format drop in excel then import into the database