Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Timestamp in MySQL
Top Forums UNIX for Dummies Questions & Answers Timestamp in MySQL Post 302144915 by nervous on Sunday 11th of November 2007 11:49:41 PM
Old 11-12-2007
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?
 

10 More Discussions You Might Find Interesting

1. UNIX and Linux Applications

create 'day' tables based on timestamp in mysql

How would one go about creating 'day' tables based on the timestamp field. I have some 'import' tables which contains data from various days and would like to spilt that data up into 'days' based on the timestamp field in new tables. TABLE_IMPORT1 TABLE_IMPORT2 TABLE_IMPORT3 ... (2 Replies)
Discussion started by: hazno
2 Replies

2. Shell Programming and Scripting

conversion of different timestamp to standard timestamp

hi i need a scrit to convert one date format to another. for example i have three columns in a file which gets a different format, but lastly i want output with stadard timestamp as "yyyy-mm-dd hh:mm:ss" column1 column2 ... (2 Replies)
Discussion started by: dprakash
2 Replies

3. Shell Programming and Scripting

Call Shell Function from mysql timestamp

Hi all, Actually my aim is to call the shell script when ever there is a hit in a mysql table which consist of 3 values. Acter some research I came to know that it is not possible and can achive with timestamp. Can someone please tell me how to read the table timestamp which should done... (3 Replies)
Discussion started by: santhoshvkumar
3 Replies

4. Shell Programming and Scripting

Getting a relative timestamp from timestamp stored in a file

Hi, I've a file in the following format 1999-APR-8 17:31:06 1500 3 45 1999-APR-8 17:31:15 1500 3 45 1999-APR-8 17:31:25 1500 3 45 1999-APR-8 17:31:30 1500 3 45 1999-APR-8 17:31:55 1500 3 45 1999-APR-8 17:32:06 1500 3 ... (1 Reply)
Discussion started by: vaibhavkorde
1 Replies

5. UNIX for Dummies Questions & Answers

How to compare a file by its timestamp and store in a different location whenever timestamp changes?

Hi All, I am new to unix programming. I am trying for a requirement and the requirement goes like this..... I have a test folder. Which tracks log files. After certain time, the log file is getting overwritten by another file (randomly as the time interval is not periodic). I need to preserve... (2 Replies)
Discussion started by: mailsara
2 Replies

6. Shell Programming and Scripting

Identifying files with a timestamp greater than a given timestamp

I need to be able to identify files with file timestamps greater than a given timestamp. I am using the following solution, although it appears to compare files at the "seconds" granularity and I need it at the milliseconds. When I tested my solution, it missed files that had timestamps... (3 Replies)
Discussion started by: nkm0brm
3 Replies

7. Shell Programming and Scripting

To check timestamp in logfile and display lines upto 3 hours before current timestamp

Hi Friends, I have the following logfile. Currently time in india is 07/31/2014 12:33:34 and i have the following content in logfile. I want to display only those entries which contain string 'Exception' within last 3 hours. In this case, it would be the last line only I can get the... (12 Replies)
Discussion started by: srkmish
12 Replies

8. Shell Programming and Scripting

AIX : Need to convert UNIX Timestamp to normal timestamp

Hello , I am working on AIX. I have to convert Unix timestamp to normal timestamp. Below is the file. The Unix timestamp will always be preceded by EFFECTIVE_TIME as first field as shown and there could be multiple EFFECTIVE_TIME in the file : 3.txt Contents of... (6 Replies)
Discussion started by: rahul2662
6 Replies

9. Shell Programming and Scripting

Grep lines between last hour timestamp and current timestamp

So basically I have a log file and each line in this log file starts with a timestamp: MON DD HH:MM:SS SEP 15 07:30:01 I need to grep all the lines between last hour timestamp and current timestamp. Then these lines will be moved to a tmp file from which I will grep for particular strings. ... (1 Reply)
Discussion started by: nms
1 Replies

10. Programming

Node-RED: Writing MQTT Messages to MySQL DB with UNIX timestamp

First, I want to thank Neo (LOL) for this post from 2018, Node.js and mysql - ER_ACCESS_DENIED_ERROR I could not get the Node-RED mysql module to work and searched Google until all my links were purple! I kept getting ER_ACCESS_DENIED_ERROR with the right credentials. Nothing on the web was... (0 Replies)
Discussion started by: Neo
0 Replies

LEARN ABOUT PHP

getdate

GETDATE(3)								 1								GETDATE(3)

getdate - Get date/time information

SYNOPSIS

       array getdate ([int  $timestamp = time()])

DESCRIPTION

	Returns an associative array containing the date information of the $timestamp, or the current local time if no $timestamp is given.

PARAMETERS

	      o $timestamp
		-  The	optional  $timestamp parameter is an integer Unix timestamp that defaults to the current local time if a $timestamp is not
		given. In other words, it defaults to the value of time(3).

RETURN VALUES

	Returns an associative array of information related to the $timestamp. Elements from the returned associative array are as follows:

       Key elements of the returned associative array

       +----------+--------------------------------------+---+
       |   Key	  |					 |   |
       |	  |					 |   |
       |	  |		Description		 |   |
       |	  |					 |   |
       |	  |	  Example returned values	 |   |
       |	  |					 |   |
       +----------+--------------------------------------+---+
       |	  |					 |   |
       |"seconds" |					 |   |
       |	  |					 |   |
       |	  |  Numeric representation of seconds	 |   |
       |	  |					 |   |
       |	  |					 |   |
       |	  |		  0 to 59		 |   |
       |	  |					 |   |
       |	  |					 |   |
       |"minutes" |					 |   |
       |	  |					 |   |
       |	  |  Numeric representation of minutes	 |   |
       |	  |					 |   |
       |	  |					 |   |
       |	  |		  0 to 59		 |   |
       |	  |					 |   |
       |	  |					 |   |
       | "hours"  |					 |   |
       |	  |					 |   |
       |	  |   Numeric representation of hours	 |   |
       |	  |					 |   |
       |	  |					 |   |
       |	  |		  0 to 23		 |   |
       |	  |					 |   |
       |	  |					 |   |
       | "mday"   |					 |   |
       |	  |					 |   |
       |	  | Numeric representation of the day of |   |
       |	  | the month				 |   |
       |	  |					 |   |
       |	  |					 |   |
       |	  |		  1 to 31		 |   |
       |	  |					 |   |
       |	  |					 |   |
       | "wday"   |					 |   |
       |	  |					 |   |
       |	  | Numeric representation of the day of |   |
       |	  | the week				 |   |
       |	  |					 |   |
       |	  |					 |   |
       |	  | 0 (for Sunday) through 6 (for Satur- |   |
       |	  | day)				 |   |
       |	  |					 |   |
       |	  |					 |   |
       |  "mon"   |					 |   |
       |	  |					 |   |
       |	  |  Numeric representation of a month	 |   |
       |	  |					 |   |
       |	  |					 |   |
       |	  |		1 through 12		 |   |
       |	  |					 |   |
       |	  |					 |   |
       | "year"   |					 |   |
       |	  |					 |   |
       |	  | A  full  numeric representation of a |   |
       |	  | year, 4 digits			 |   |
       |	  |					 |   |
       |	  |	   Examples: 1999 or 2003	 |   |
       |	  |					 |   |
       |	  |					 |   |
       | "yday"   |					 |   |
       |	  |					 |   |
       |	  | Numeric representation of the day of |   |
       |	  | the year				 |   |
       |	  |					 |   |
       |	  |					 |   |
       |	  |	       0 through 365		 |   |
       |	  |					 |   |
       |	  |					 |   |
       |"weekday" |					 |   |
       |	  |					 |   |
       |	  | A full textual representation of the |   |
       |	  | day of the week			 |   |
       |	  |					 |   |
       |	  |					 |   |
       |	  |	  Sunday through Saturday	 |   |
       |	  |					 |   |
       |	  |					 |   |
       | "month"  |					 |   |
       |	  |					 |   |
       |	  | A full textual representation  of  a |   |
       |	  | month, such as January or March	 |   |
       |	  |					 |   |
       |	  |					 |   |
       |	  |	  January through December	 |   |
       |	  |					 |   |
       |	  |					 |   |
       |    0	  |					 |   |
       |	  |					 |   |
       |	  |  Seconds since the Unix Epoch, simi- |   |
       |	  | lar  to  the  values   returned   by |   |
       |	  | time(3) and used by date(3).	 |   |
       |	  |					 |   |
       |	  |  System	Dependent,     typically |   |
       |	  | -2147483648 through 2147483647.	 |   |
       |	  |					 |   |
       +----------+--------------------------------------+---+
EXAMPLES

       Example #1

	      getdate(3) example

	      <?php
	      $today = getdate();
	      print_r($today);
	      ?>

	      The above example will output something similar to:

	      Array
	      (
		  [seconds] => 40
		  [minutes] => 58
		  [hours]   => 21
		  [mday]    => 17
		  [wday]    => 2
		  [mon]     => 6
		  [year]    => 2003
		  [yday]    => 167
		  [weekday] => Tuesday
		  [month]   => June
		  [0]	    => 1055901520
	      )

SEE ALSO

       date(3), idate(3), localtime(3), time(3), setlocale(3).

PHP Documentation Group 														GETDATE(3)
All times are GMT -4. The time now is 05:25 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy