473,695 Members | 2,418 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need help how to do Time format in MySql

19 New Member
hi all,


I have time stored in my table field as hh:mm am or pm (eg 04:15 PM , 02:30 AM). The field is of type varchar.

I need to convert it as a MySql time format.

Can any body help me...Please .. Its urgent
Jun 20 '07 #1
6 3749
r035198x
13,262 MVP
hi all,


I have time stored in my table field as hh:mm am or pm (eg 04:15 PM , 02:30 AM). The field is of type varchar.

I need to convert it as a MySql time format.

Can any body help me...Please .. Its urgent
If you're using MySQL 5 or higher you can use the str_to_date function. Otherwise you'd have to do it outside the database.
Jun 20 '07 #2
pbmods
5,821 Recognized Expert Expert
Here are your options for pre-5 MySQL:
http://dev.mysql.com/doc/refman/4.1/...functions.html

And for MySQL 5:
http://dev.mysql.com/doc/refman/5.0/...functions.html
Jun 22 '07 #3
ponvijaya
19 New Member
Hi,

Thanks for your suggestion. I have already seen the link provided by you. But could get idea which funtion and forma to use..

I am using Mysql version 5.1.

Can u tell me the exact function format to get my required result. I need to generate the report based on ascendin order of time.

Since my time field is of varchar type, i am getting the display is String order not in the actual time order what i need.. Thats what i need to convert the varchar format to time format.

Please help me.. My work is pending because of this..

Looking forward for your help at the earliest..




Here are your options for pre-5 MySQL:
http://dev.mysql.com/doc/refman/4.1/...functions.html

And for MySQL 5:
http://dev.mysql.com/doc/refman/5.0/...functions.html
Jun 25 '07 #4
tintino
2 New Member
date_format(str _to_date('12:24 am','%h:%i %p'),'%T')
returns 00:24:00

date_format(str _to_date('01:24 pm','%h:%i %p'),'%T')
returns 13:24:00
Jun 25 '07 #5
tintino
2 New Member
to just order your result set right, you could use something like this
(even in older mysql versions)

say your time field is named "clock"...

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.           (clock LIKE '%pm%') as t ,  *
  3. FROM   tbl
  4. ORDER BY  clock,t
  5.  
this would just add a virtual column t, containing:
- 0 where clock doesn't contain 'pm' (where clock is am)
- 1 where clock contains 'pm'

so you order your set by clock, and then t
Jun 25 '07 #6
ponvijaya
19 New Member
Hi i have used this format function
date_format(str _to_date(startt ime,'%h:%i %p'),'%T')

which gave me the required result.


Thanks a lot for your valuable help. Thank you once again.

date_format(str _to_date('12:24 am','%h:%i %p'),'%T')
returns 00:24:00

date_format(str _to_date('01:24 pm','%h:%i %p'),'%T')
returns 13:24:00
Jun 25 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

12
3675
by: Rick | last post by:
I need to store a date prior to the unix epoch, any pointers? -- Rick Digital Printing www.intelligence-direct.com
1
24599
by: Neal | last post by:
I have a MySQL table with a datetime field that stamps the time the user submitted the form. It works and looks like this: $sql = "INSERT INTO table VALUES(....now())"; On another php page to view results, the code: printf("<br><td> %s</td>\n", $newArray);
1
508
by: Dave Posh | last post by:
I seem to be having a problem displaying time stored in mysql. The format stored in the database is 13:15:05. The database data type is time. I'm using asp vbscript and sql to retrieve the time store in the database. However asp recognizes the data type as date and displays the date instead of the time. If I change the data type in mysql to varchar then asp will display the time correctly, but I cannot do it this way. I want to be able...
3
2654
by: parksch2 | last post by:
I have been trying and trying to properly sort records through a mySQL query. I have hour, minute and AM/PM values stored separately in a database as varchars. I'm trying to concat those, cast them to a time value and sort them. I have found that it sorts some properly but other it does not. The following query: CAST(CONCAT(hour, ':', minute, ' ', ampm) AS TIME) AS myTime ORDER BY myTime
2
1491
by: phillip.s.powell | last post by:
I have to migrate data from one database table to another table in another database where the fields do not match, not even in the same order, and even if they do match, on occasions the datatypes are completely different (varchar vs int, varchar vs char, int vs datetime, etc.). I am expected to do this immediately so this is an emergency request. Help!
15
4605
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to communicate with a MySQL database table on a web server, from inside of my company's Access-VBA application. I know VBA pretty well but have never before needed to do this HTTP/XML/MySQL type functions.
9
1602
by: Paul | last post by:
I have a process that I want to speed up. It first was written in Microsoft Access then converted to VB.NET. I would like to hear some suggestions on how to speed it up. The process is to match names using a Soundex function. It reads through a table of about 20,000 records and matches it with a table of about 9,000 records. That is all done in code. The tables are stored in SQL Server 2005. Soundex is a numerical formula to...
5
20837
by: rouven | last post by:
Hi, i am trying to convert that time format '05:26:40 Jun 19, 2007 PDT' into mysql compatible format like YYYY-MM-DD HH:MM:SS. the code i tried was: from datetime import datetime from time import strptime f1.write("strptime(%r); locale: %r" % (olddate, locale.getlocale())) newdate = time.strptime(olddate, "%H:%M:%S %b %d, %Y %Z") mysqldate = time.strftime("%Y-%m-%d %H:%M:%S", newdate)
2
3106
by: daknightuk | last post by:
Hi people, I'm working on a project where I have a MYSQL database containing a database table which has opening hours of a load of businesses in it. Each business has 4 TIME fields for each day in the format 00:00:00 which are named to indicate the weekday i.e MONOPEN1, MONCLOSE1, MONOPEN2, MONCLOSE2, TUEOPEN1......... My original idea was to output it as a table in the format Monday 10:00-12:00
0
8638
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8578
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9120
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8854
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8831
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4345
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3013
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2278
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1979
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.