dates in mysql
#1
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 Smile
http://www.onlineconversion.com/unix_time.htm
A little higher than Rookie status... getting better very day..

Debbie D.
Reply
#2
php has a built in date function for this, you know.
good u
Reply
#3
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 Smile
well FYI I did get some 60-odd events added in in an hour.. pretty good I think Smile forumla or no formula Smile but good to know because I know there are a ton more to be added
A little higher than Rookie status... getting better very day..

Debbie D.
Reply
#4
This user has been denied support. This user has been denied support.
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.
Reply
#5
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.
[Image: LogoO.png]
Reply
#6
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
Lost interest, sold my sites, will browse here once in a while. It's been fun.
Reply
#7
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


A little higher than Rookie status... getting better very day..

Debbie D.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)