Unix/Linux Go Back    

UNIX for Dummies Questions & Answers This forum is closed for new posts. Please post beginner questions to learn unix and learn linux in this forum UNIX for Beginners Questions & Answers

Timestamp in MySQL

UNIX for Dummies Questions & Answers

Thread Tools Search this Thread Display Modes
Old Unix and Linux 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
Old Unix and Linux 11-12-2007
tayyabq8's Unix or Linux Image
tayyabq8 tayyabq8 is offline Forum Advisor  
Join Date: Nov 2004
Last Activity: 31 January 2017, 1:29 AM EST
Location: Oman
Posts: 593
Thanks: 8
Thanked 12 Times in 5 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

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux 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 03:52 AM.