/    Sign up×
Community /Pin to ProfileBookmark

Converting date to a Unix Timestamp

Copy linkTweet thisAlerts:
Jul 10.2004

I am in the process of transfering all of the content from my existing blog to a MySQL database. One thing I would like to change is to stop using strings such as “July 9th” for the date, and start using Unix timestamps instead. One problem I’m having is I have the “Date” field in my database has a TIMESTAMP type, therefor every entry must contain a timestamp in the date field. I need to convert the existing dates to Unix timestamps. How might one go about doing this?

to post a comment
PHP

9 Comments(s) ↴

Copy linkTweet thisAlerts:
@Paul_JrJul 10.2004 — You're probably looking at a long process of manually converting them with [url=http://us2.php.net/mktime]mktime();[/url]
Copy linkTweet thisAlerts:
@Daniel_TauthorJul 10.2004 — [i]Originally posted by Paul Jr [/i]

[B]You're probably looking at a long process of manually converting them with [url=http://us2.php.net/mktime]mktime();[/url] [/B][/QUOTE] Well, looks like another allniter for me then! Thanks for the link, Paul!
Copy linkTweet thisAlerts:
@Paul_JrJul 10.2004 — [i]Originally posted by Daniel T [/i]

[B]Well, looks like another allniter for me then! Thanks for the link, Paul! [/B][/QUOTE]
Lol, no problem. I feel real crappy, so I'ma hit the sack (wow, a whole half-hour earlier than I usually decide to go to bed, and a whole two-and-a-half or so hours before I actually get in bed).
Copy linkTweet thisAlerts:
@ConorJul 10.2004 — just put your times in an array and run a loop to convert all of them
Copy linkTweet thisAlerts:
@Daniel_TauthorJul 10.2004 — K, I've converted the dates to timestamps for the entries, now I'm trying to add them to the database. However, when I go to edit a row and put the timestamp in the Date field(with a "TIMESTAMP" type), and save it, it always puts it at 00000000000000, even though I entered the correct timestamp. How do I stop it from doing this?

Dan

PS: This is in phpMyAdmin.
Copy linkTweet thisAlerts:
@Paul_JrJul 11.2004 — [font=georgia]Y’know what is really, really weird? The same thing is happening to me. It appears I cannot even insert timestamps through PHP, which seems odd, because I could do it just fine before…

I have a theory about this: Supposedly, [url=http://www.php.net/time]time();[/url] returns the current time in Unix Timestamp format; the returned value is only 10 characters in length. The MySQL type has a length of 14 characters. I am thinking that since the timestamp entered is not of the correct length, it’s being rejected. I figured out that a 14 character long unix timestamp is not only a timestamp of the current date, including year, month, day, hour and second, but also microseconds. That is very confusing to me… Anyway, I picked a random 4 numbers and added them onto the end of my timestamp and tried inserting into the database — still no go. Which now leads me to believe that my original theory that the timestamp is being rejected because of its length is false. Which [i]then[/i] leads me to believe that I am as lost as you are… ? ?[/font]
Copy linkTweet thisAlerts:
@JonaJul 11.2004 — [font=arial]Make it VARCHAR instead of DATE. Works for me. :p [/font]
Copy linkTweet thisAlerts:
@Paul_JrJul 11.2004 — [i]Originally posted by Jona [/i]

[B][font=arial]Make it VARCHAR instead of DATE. Works for me. :p [/font] [/B][/QUOTE]
[font=georgia]But, but, but, that’s [i]wrong![/i] And it still doesn’t explain why this is failing after so many months of working. ?[/font]
Copy linkTweet thisAlerts:
@Daniel_TauthorJul 11.2004 — [i]Originally posted by Jona [/i]

[B][font=arial]Make it VARCHAR instead of DATE. Works for me. :p [/font] [/B][/QUOTE] No, the field type isn't date, that's just the name of the field. The field type is TIMESTAMP. Anyways, I switched the type to INT, and it works fine, so I'll just have to live with it.
×

Success!

Help @Daniel_T spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.9,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...