Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Donnie Driskell
Donnie Driskell
2,243 Points
Posted by Donnie Driskell
Donnie Driskell
2,243 Points

Numerical and Date Conversions

This is way ahead of where I am in my lessons. However, since I have a real database that I work with, there is some date info in the columns that is numeric.

As an example, I have a Forum and these are the columns with real column names and real numbers. I need to convert this info to a Month, Day, Year and also HH:MM:SS I need to find out when a particular user last visited or their last post time was. Is anyone willing to help me with this? thanks Donnie D.

user_lastvisit 1510337665 user_lastpost_time 1510241683

3 Answers

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

It seems odd that the database isn't displaying these values as dates and times automatically, but assuming you are working with SQLite, you can coerce it like this:

SELECT DATETIME(user_lastvisit, 'unixepoch') as user_lastvisit,
       DATETIME(user_lastpost_time, 'unixepoch') as user_lastpost_time

For the values shown, this produces:

user_lastvisit         user_lastpost_time
-------------------    -------------------
2017-11-10 18:14:25    2017-11-09 15:34:43

If you're using a different database it may have a different syntax for date conversions.

Donnie Driskell
Donnie Driskell
2,243 Points
Donnie Driskell
Donnie Driskell
2,243 Points

Steven, thank you very much. This is really good. I had been searching online 'google' on and off for a few days and never came up with exactly what I was looking for. I think what is going on with the forum I manage is that the MYSQL is unix based dating. Then the php forum picks up with the calculation and displays that info in readable format for users. I am new to all of this and just finished my Basics SQL and now I am in Reporting Results tutorial. I have been using what I learn here on my real database and so far, the only difference is that where SQL lite might use " ", the MySQL uses ' '. Thanks again, i will use that SELECT KEYWORDs and see what happens. I just need to find out who has not posted to my forum in a long time.

Donnie

Donnie Driskell
Donnie Driskell
2,243 Points
Donnie Driskell
Donnie Driskell
2,243 Points

Well, actually, the keywords returned an error, however, I was able to google again "DATE FUNCTIONS" and I came across a nice MYSQL functions list and made a very simple SELECT statement --- taken from your syntax. So, still , your help was very nice in the fact that it got me to thinking in another way using your syntax and finally I was able to return the values that I wanted !!! This is what I used: SELECT FROM_UNIXTIME( user_lastvisit ) , FROM_UNIXTIME( user_lastpost_time ) , username FROM phpbb_users LIMIT 0 , 30

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

You'll find that every database does certain things (like dates) a bit differently.

Anyway, glad to help. And happy coding!