Blackscooby

This one is giving me some greif....

How do I convert a date which is in unix format ie 1026932823 to a regular date format under SQL ???

I can work it out manually (well via a Web site), but I've got potentially thousands to convert

Ta muchly.

Mark

stevem2k

Have a hack around with this ...

Call amtime.tcl -d $DATE_STRING from inside a simple for loop reading each DATE_STRING in turn and output.

Not exactly elegant, but it should work for mass conversions..

Steve

http://www.unixreview.com/documents/...104k/0104k.htm

#!/usr/bin/tclsh
# Name: amtime.tcl
# Author: Andrew Barber
# Purpose: Convert from
"UNIX time in seconds" (UTIS) to
# a usable date/time string.
# It imitates the SGI availibility time conversion tool
# amtime1970.
# Tool uses the same flags as amtime1970.
# Advantages: Will run any where there is TCL
# Set some inital varialbles.
# Type of conversion to perform. Specified via command-line
# options below.
# -t to convert from UTIS to a "readable" date/time string.
# -d to convert from a "readable" date/time string into UTIS.
set TYPE_FLAG ""
# Text to print out.
set OUTPUTSTR ""
# exit return flag.
set EXIT_LEVEL 0
# Define some procedures if we aren't using TclX. This assumes TCL >8.x
#
# Define the "getclock"
function to return UTIS.
if {[info commands getclock]
== ""} {

proc getclock {} {

return [clock seconds]

}
}
# Define the fmtclock function to format the date/timeutput/conversion from UTIS.
if {[info commands fmtclock]
== ""} {

proc fmtclock {clock_value {format_string {}}} {

set clock_command "clock format $clock_value"

if {$format_string != ""} {

append clock_command " -format $format_string"

}

set return_value [eval $clock_command]

return $return_value

}
}
# Define the convertclock function to convert from date/time to UTIS.
if {[info commands convertclock]
== ""} {

proc convertclock {datetime} {

return [clock scan "$datetime"]

}
}
# Parse the command-line arguments. Unknown arguments are just skipped
for {set cntr 0} {$cntr
< [llength $argv]} {incr cntr} {

set argument [lindex $argv $cntr]

switch -- $argument {

"-t" {

# Set the type of conversion to perform.

set TYPE_FLAG "T"

# go to the next argument index in the argument array.

incr cntr

# Get the next argument.

set argument [lrange $argv $cntr end]

# Get UTIS from "standard date/time"

set OUTPUTSTR [convertclock "$argument"]

}

"-d" {

# Set the type of conversion to perform.

set TYPE_FLAG "D"

# go to the next argument index in the argument array.

incr cntr

# Get the next argument.

set argument [lindex $argv $cntr]

# Check that the argument is a number.

if {[catch {expr $argument + 1}]} {

set OUTPUTSTR "ERROR: Wrong argument: $argument"

set EXIT_LEVEL 1

} else {

# Convert the UTIS into date/time format.

set OUTPUTSTR [fmtclock $argument "%c"]

}

}

}
}
# See if we had any arguments passed. If no args, output UTIS for the current date/time.
if {$TYPE_FLAG == ""}
{
#
set OUTPUTSTR [convertclock [fmtclock [getclock] "%c"]]

set OUTPUTSTR [getclock]
}
puts "$OUTPUTSTR"
exit $EXIT_LEVEL

stevencotton

To convert epoch seconds into something readable:

perl -e 'print scalar localtime(1026985645), "\n"'

If you want a specific format you can use strftime():

perl -MPOSIX -e 'print strftime("%a %b %y %Y", localtime), "\n";'

man strftime for formatting options. Wont help you if you need a pure SQL solution though, but you could always loop through them all.

Steve.

orbv

What SQL server backend? Most have built in functions for doing this try of conversions.

With mysql look here, eg.
FROM_UNIXTIME(timestamp)
UNIX_TIMESTAMP(date)

Also what language you programming in? Most have time convertion functions eg, under php getdate.