Timestamp in MySQL | Unix Linux Forums | UNIX for Dummies Questions & Answers

  Go Back    


UNIX for Dummies Questions & Answers If you're not sure where to post a UNIX or Linux question, post it here. All UNIX and Linux newbies welcome !!

Timestamp in MySQL

UNIX for Dummies Questions & Answers


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 11-11-2007
nervous nervous is offline
Registered User
 
Join Date: Sep 2006
Last Activity: 4 December 2008, 9:27 AM EST
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Timestamp in MySQL

Someone please help me with MySQL. I have a field in the table that contains the UNIX timestamp. Using PHP, I can format that timestamp in any way I like.

Now, I want to fetch rows for a particular date. For example, all rows having timestamp equivalent to 11th November 2007 or all rows having timestamp equivalent to 7th January 2006.

One method is to fetch all rows using MySQL query and then using PHP, filter them using If-Else and the PHP function I use to format the timestamp. I can do it easily but this method is not the right way and it may create problems later when the data grows.

What's the other way? Actually I want to filter the records through the MySQL query. It should be something like this: "select * from mytable where timestamp=____". As you know, timestamps are the seconds and for each day (e.g 11th November 2007), their values will be different for all the rows belonging to that date. In short, I want to convert timestamp to ordinary date in the MySQL query and probably using any MySQL function.

Anyone has any idea how to proceed?
Sponsored Links
    #2  
Old 11-12-2007
tayyabq8's Avatar
tayyabq8 tayyabq8 is offline Forum Advisor  
Ex-Moderator
 
Join Date: Nov 2004
Last Activity: 16 December 2013, 12:55 AM EST
Location: Bahrain
Posts: 592
Thanks: 3
Thanked 8 Times in 3 Posts
Try FROM_UNIXTIME(unix_timestamp,format)

e.g. 1194873780 = 12th Nov 2007 @ 08:23am, then

FROM_UNIXTIME( 1194873780, '%d %m %Y');

would yield 12 11 2007, You can format it the way you want, for more details about date formatting check DATE_FORMAT(date,format) function in MySQL.

About UNIX timestamp:

The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970.
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Identifying files with a timestamp greater than a given timestamp nkm0brm Shell Programming and Scripting 3 04-10-2012 07:22 PM
How to compare a file by its timestamp and store in a different location whenever timestamp changes? mailsara UNIX for Dummies Questions & Answers 2 07-25-2011 03:48 PM
Getting a relative timestamp from timestamp stored in a file vaibhavkorde Shell Programming and Scripting 1 04-27-2011 06:08 AM
Call Shell Function from mysql timestamp santhoshvkumar Shell Programming and Scripting 3 04-25-2011 09:34 AM
create 'day' tables based on timestamp in mysql hazno UNIX and Linux Applications 2 03-10-2009 07:49 AM



All times are GMT -4. The time now is 10:18 PM.