Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

Convert a text date to unix date format for importing to DB

This occurs in my raw data: '8/31/2009 12:00:00 AM'

I want simply 2009-08-31 and ignore the time part

In my text editor I can use grep find-and-replace to 2009-8-31:
(\d\d?)/(\d\d?)/(\d\d\d\d) 12:00:00 AM,
\3-\2-\1
but how do I get a leading zero to occur when needed. Maybe this calls for sed?
0
timprotech
Asked:
timprotech
1 Solution
 
hampus_bCommented:
from the zones i figure you are trying to work with a regexp in a database.

take a look at this post: http://www.timflight.com/mysql-update-query-to-convert-date-formats-mmddyyyy-to-yyyy-mm-dd/#comment-26788
0
 
dcesariCommented:
It is not clear from your post whether you have to do the conversion within a bash shell script or something else; if this is the case (bash/ksh), then an efficient way to extract the date from your initial format and reformat it with the necessary leding zeroes is shown in the attached script, it uses only shell builtins ${var%}, ${var#} and printf.


#!/bin/bash
 
raw_data='8/31/2009 12:00:00 AM'
 
month=${raw_data%%/*} # remove longest occurrence of /*
raw_data=${raw_data#$month/} # remove $month/ from $raw_data
day=${raw_data%%/*} # remove longest occurrence of /*
raw_data=${raw_data#$day/} # remove $day/ from $raw_data
year=${raw_data%% *} # remove longest occurrence of /*
 
# output with the desired format
printf "%04d-%02d-%02d\n" $year $month $day

Open in new window

0
 
marchentCommented:
Perl,
$date = '8/3/2009 12:00:00 AM';
$date =~ s/^(\d+)\/(\d+)\/(\d+).*/sprintf("%04d",$3)."-".sprintf("%02d",$2)."-".sprintf("%02d",$1)/e;
print "$date\n";

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
marchentCommented:
if you want year, month, day format,
$date = '8/31/2009 12:00:00 AM';
$date =~ s/^(\d+)\/(\d+)\/(\d+).*/sprintf("%04d",$3)."-".sprintf("%02d",$1)."-".sprintf("%02d",$2)/e;
print "$date\n";

Open in new window

0
 
woolmilkporcCommented:
If you have GNU date (what is your platform?) simply do -
date -d "8/31/2009 12:00:00 AM" +"%Y-%m-%d"
That's all
wmp
 
0
 
timprotechAuthor Commented:
To be clearer, I think this is more of a sed/awk problem to the whole text file before importing into the DB. Note: it has to take into account the single digit months and days, padding them with zeroes.

From this:

d9043f33,Globally Harmonized System - 2009 Status and Strategies,8/31/2009 12:00:00 AM,2:35 pm,3:15 pm,"Mr Daniel Franks, CHMM",2009 National Conference
030da9df,How to Succeed in the Environmental Business Without Really Trying.,8/31/2009 12:00:00 AM,2:35 pm,3:15 pm,"Mr Matthew J Cochise, CHMM",2009 National Conference
a9e0dace,MSDS Management 101,8/31/2009 12:00:00 AM,11:05 am,11:45 am,Joe Miller,2009 National Conference

To this (see 3rd comma-separated field):

d9043f33,Globally Harmonized System - 2009 Status and Strategies,2009-08-31,2:35 pm,3:15 pm,"Mr Daniel Franks, CHMM",2009 National Conference
030da9df,How to Succeed in the Environmental Business Without Really Trying.,2009-08-31,2:35 pm,3:15 pm,"Mr Matthew J Cochise, CHMM",2009 National Conference
a9e0dace,MSDS Management 101,2009-08-31,11:05 am,11:45 am,Joe Miller,2009 National Conference

0
 
woolmilkporcCommented:
GNU date does padding!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now